Data Table: Automatically Updating Source and Target Sheets

Elizabeth N

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!!!

kowal

hi @Elizabeth N,

if you need more than 20k rows in the sheet and you have DataTable I would keep all my data in Datatable.

You can add extra column with checkbox to mark executive or non-executive. Based on that you can always work in the sheet with data and all the formulas but using the filter make sure that there is less than 20k rows in the sheet.

Elizabeth N

@kowal, thanks for the insight!

Do you know any tips for updating/replacing the data on the Data Table? We run the same report at least once a quarter and will need the updated data to pull into the sheets and dashboards.

I have a "Report Date" column on the Source and tried to pull in the rows from the Data Table to a filtered sheet based on Executive and the Report Date, but it doesn't seem to be working.

Here is the workflow:

Here is the Source (you can see there is at least one Executive with today's Report Date):

But on the Data Table, it is not updating with the new data from today's date (there should be 23000+ rows now based on the source file, but the Data Table is still only showing the original 20,062) :

kowal

hi @Elizabeth N ,

so data table has dynamic connection with external CSV files - so it depends where is the data for your datatable coming from maybe you can make this trick (I have never tried that but it may work) if you have data shuttle you can setup the connection for the big CSV file to go into datatable and you can connect the same CSV with your smartsheet sheet other way so whenever you will make changes in the sheet it will be visible in the CSV. Would this work?