Hi all! Coming to the experts for this…
My company has 20k+ employees, so I cannot have all their employee information on one sheet. Instead, I've broken it up into two different sheets: one for executives; and one for non-executives. I'll refer to these as the "main sheets."
I also have different sheets for each of our different classes with a full list of each employee that has completed each respective class. I built VLOOKUPs on the "main sheets" that connect to these learning history sheets so I can see which classes each employee has attended.
The challenge I am running into is when we need to update our "main sheets." We re-run the report in our Learning system and then I need to bring that data into Smartsheet (we do this because we also have columns for their HR rep; when they were last promoted; etc.). I do not want to manually copy and paste the new data into the main sheet, as Smartsheet only allows you to do so for 500 rows at the time and doing this for 20k+ employees seems tedious; but I also do not want to create a new sheet with the updated data because then I will need to recreate all the VLOOKUPs and formulas I have in the current "main sheets" and reconnect to our dashboards.
I tried using Data Table to upload the file with 20k rows; then connected it to two different sheets filtering one for executives and one for non-executives. I was figuring that I could delete the inactive employees from the source sheet in Data Table, but when I delete the row there, it does not automatically delete it on the target sheet. I also figured I could just write over the current data with the new data, but it seems to add the new data to the bottom while keeping the old written-over data still.
I'm at a loss...what is the best way to handle updating this much data without it impacting our formulas? Open to any ideas....
Thank you!!!