Google Sheet Back End Data Integrity

0
votes

I have a Google Sheet used for employee time entry on Sheet1 that collects data into a second back end "Data" sheet which uses a lot of VLOOKUP functions based on other sheets. There have been some issues with people cutting and pasting or dragging cell/row positions which causes REF errors and also throws off the VLOOKUP formulas on the Data sheet. I am not sure VLOOKUP is the way to go with this as badly as the references can get borked. If anyone can suggest a better way of getting the data in the data sheet feel free to let me know. Sheet is shared here and pretty well sanitized.

https://docs.google.com/spreadsheets/d/1W7YFNkKC-Ruelvls_kJKsnuxYkJn8O5WuZmhKhUhbh4/edit?usp=sharing

1
google-sheets

1 Answers

0
votes

You can use the OFFSET function in which you can set it to only reference the header rows. The header rows usually do not get cut and pasted else where making it a fairly stable reference.

OFFSET In Action:

In your Data sheet in cell C2 you can use this formula instead.

=IFERROR(VLOOKUP(OFFSET(Sheet1!$A$1,ROW()-1,0),Employees!A$2:B$100,2,false),"")

If you take this approach for the other formulas you should be able to get to a more stable file.

EDIT: I also wanted to mention if others are using this file you can protect the header range making it impossible to mess up the formulas.