I've been looking for answers to my need, but seem to keep finding answers to the opposite problem. Here is my challenge.

I have a google sheet to process payroll. On the first tab is data submitted via a form from my staff - their timesheet. All that data is reference by tab 2 in order to prepare a template for uploading to our payroll processor.

Whenever I sort the data in tab 1 by name, to keep it alphabetical as we add and remove staff, the cell references get thrown off.

I need a way to tell it, "no matter what line John Smith moves to, always reference the data from that line and place it in the designated line in tab 2."

How do I do this?

  • could you add examples? or share a copy of your sheet? – MARK MY ANSWER Sep 11 '18 at 1:07
  • I want Gusto: C6 to always reference what is now Staff: H20 even if I sort the Staff tab and the contents of H20 end up in a different cell due to sorting. docs.google.com/spreadsheets/d/… – Brad Larsen-Sanchez Sep 12 '18 at 5:14
  • How about Named ranges? You can name the cell or cells you need to reference, and then no matter where they go, the reference never breaks. – Ron Kloberdanz Sep 14 '18 at 21:32
  • I got excited about named ranges, haven't heard of it before. But by following the directions in the link you provided, the same issue results. I can't seem to figure it out. – Brad Larsen-Sanchez Sep 15 '18 at 22:08

Absolute locked reference looks like this:

  • $A$1
  • Sheet1!$A$1
  • 'Sheet 1'!$A$1

but you probably want this: =ARRAYFORMULA(IF(LEN(A2:A); VLOOKUP(A2:A; 'Tab B'!A2:B; 2; 0); ))

which means that IF ID from Tab B is found in Tab A, formula will return 2 (second) column from range A:B of Tab B in sorted order based on IDs in A column of Tab A - hence that 0

Here is demo spreadsheet: https://docs.google.com/spreadsheets/d/

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.