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?

  • 1
    could you add examples? or share a copy of your sheet?
    – user0
    Sep 11, 2018 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/… Sep 12, 2018 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. Sep 14, 2018 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. Sep 15, 2018 at 22:08

1 Answer 1


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/


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