How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask John D Your Own Question
John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9666
Experience:  Worked on Macs and PC's for 20+ years.
Type Your Mac Question Here...
John D is online now
A new question is answered every 9 seconds

I created 2 spreadsheets, One is the master, the 2nd is one

Customer Question

I created 2 spreadsheets, One is the master, the 2nd is one I created, with "paste special" entries from the master. Suddenly on my 2nd page everything is reading one line the wrong lines. ie. the infor should be taken from line 48 and its taking it from line 49. I've been working on this spreadsheet for 2 weeks as there are a lot of entries. I was down to my bosses last review and ready to send out, and suddenly its all wrong. On my master page I have no fancy formulas just straight input data.
Submitted: 6 years ago.
Category: Mac
Expert:  John D replied 6 years ago.



If you have pasted the data onto the second sheet as values using the Paste Special command the pasted data cannot 'read' values from other lines. It'll be just fixed numbers.


Would you be able to send me the file so I can take a look at the data



Customer: replied 6 years ago.
Yes, I can send file
Expert:  John D replied 6 years ago.

Ok go to and upload the file (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.


If the file has sensitive information let me know before you upload it


Customer: replied 6 years ago.
yes it has sensitive info
Expert:  John D replied 6 years ago.

Ok first try to replace the sensitive data with dummy values (you can delete most of the rows leaving only a few). Then go to this site to upload the file

After uploading you will be given two links, one for Downloading and the other for Deleting the file. Copy BOTH links and paste them in your reply so I can use the Delete link to delete the file from that website as soon as I have downloaded it


Customer: replied 6 years ago.

hope i did correct. the "all jobs" tab is my master, it has only straight typed in info.
the "all editor WCopy" is the one that I would cmd C from the all jobs and then
cmd D "paste special - link" to create that page. I've been doing this for years, suddently late yesterday, everything went crazy. I also have a copy of what the page should looke like. I sent that in the 2nd upload. If you look at the first person on the "all editor" - XXXXX XXXXX. lines 6 is correct, then it all goes wrong on line 7, its pulling from my all jobs line49, and it should be pulling from line48 then the next entry is pulling from 83 instead of 82. At first I thought it was one line off throughout the form, but then i found others were randomly off.
Expert:  John D replied 6 years ago.

Ok I got the two files and deleted them both from that site. Will be looking at them shortly



Expert:  John D replied 6 years ago.

I am not able to see the problem. Which of the two sheets has the issue and how would I find out which rows/columns are shifted


Customer: replied 6 years ago.
the "all jobs" is my master, that feeds into all my other sheets, not just the "all editor" tab I sent you. I remember using a filter to change the order from job# XXXXX to editor order to double check my work, I must have saved it that way. but I tried putting it back and just re-filtering by job #, but its not working. I think I also may be added or deleted a blank cell or something.
Expert:  John D replied 6 years ago.

So the problem is in the All editor tab. Ok I see formulas on that sheet referencing another file which I do not have.


I still need to know what the problem is, can you give an example (i.e. one cell, one row, etc) so I can follow





Customer: replied 6 years ago.
on the all editor's tab. take XXXXX XXXXX.
the first line of his chart (line 6) that info should be taken from line 7 of all jobs. so would read 31043 Care 1st / his second line 7 should be taken from line 48 from all jobs. and it would read 31008 Amex Heros. If you see the second file I sent you that what it shows, how it should read
Expert:  John D replied 6 years ago.

I see. Ok you need to have formulas on the 'all editor' sheet which pull data from 'all jobs' based on the person's name and Job#. This means the existing formulas will be overwritten


Will start on it shortly and get back to you asap



Customer: replied 6 years ago.
yes, but they are already in the sheets, just somehow got in the wrong order.
If you are going to cut and paste from that sheet, that's what I'm doing now myself.

Are you able to do this all for the $58 I paid? it needs to look like the sample I showed you, but please note editors fees column and the rep fees column figures all come from other spreadsheets. these figures come in from other tabs as a paste link as well.
I didnt' send you those pages, as I was hoping we could just get the master page to get back in line to follow into the "all editors'" page
Expert:  John D replied 6 years ago.

Ok I wrote the formulas that refer to the 'all jobs' sheet fixed as shown in the yellow cells below. The problem is that there are over 400 lines on that sheet. Do you want me to fix them all?



John D and 3 other Mac Specialists are ready to help you
Customer: replied 6 years ago.
Unfortunately this is what I've already started to do, this won't help me. I was hoping we could re-master the "All Jobs" page back to where it was before i screwed up, so the other page would fall back in automatically without any other manipulation.
Expert:  John D replied 6 years ago.



There are VLOOKUP formulas in the yellow cells which dynamically look for the Job# on the other sheet. The formulas you had were static formulas and are not copyable to the cells below. With these formulas you can type a Job# in column A on the All Editor sheet and the formulas will automatically pull the data for that job number


Let me send you the Excel file so you will know what I mean. Give me a few minutes to upload it ...



Expert:  John D replied 6 years ago.

Ok have a look at the formulas in the yellow cells and get back to me so we can continue