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:
- 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?
- 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.
- 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)?
