Cross-sheet reference: best way to reference cell data in the new sheet

KarolinaB

Hi,

I have employee data in one sheet (Employee List 1) generated from our HR software - the data updates nightly (employee info changes, new rows for new hires are added, etc.). That sheet can only have TEXT ONLY columns.

I created a 2nd sheet (Employee list 2) so that I can use that to add automations using different cell types, etc. and copy rows to other sheets from that sheet based on these additional criteria.

Problem: I need all the cells in Employee List 2 be exactly as they appear to Employee List 1.

Here are some things I tried/considered:

1. Automation to copy rows from Employee List 1 to Employee List 2 when rows are changed. The problem here is that it will duplicate all rows EVERY time the data is dumped in List 1, which will create duplicate values in List 2. I cannot add any additional columns to List 1 or change it in any way to create a condition where it would only COPY new rows. Is there a way to only copy new rows when they are added to make sure I avoid duplicate rows?
2. I used "Link Cell from another Sheet" in Employee List 2 linking rows to Employee List 1. The problem here is that when new rows are added, those rows are not linked or would have to be manually linked every time new hires are added. That's not going to work for me.
3. INDEX/MATCH does not work because I have to have a reference column, but I cannot have that if the data is not "copied" to List 2 in the first place and same is with VLOOKUP.

Is there a formula that returns the same value of the cell from another sheet without referencing something else (like Index/match and vlookup)?

Kerry St. Thomas

Your answer is Premium apps:

Data Shuttle can offload your List 1 every day, and then upload only those rows with employee numbers that aren't on List 2 - and separately, update those rows where the data from List 1 is different than List 2 for those rows with employee numbers that are on both lists.

This is BY FAR the most scalable method to accomplish what you're after.

This one seems kind of tough - Good luck!

KarolinaB

Thank you!

We are looking into investing in Data Shuttle in the future, but it is not what we currently have. In the meantime, I am trying to figure out a way to work around it.