Absolute References [duplicate]

Question

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?

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/