How do I overwrite or replace a sheet which is linked with multiple reports/reference sheets etc,.
I have a master data file which has around 20000 rows with nearly 10 columns (200000 Cells) Data driving several reports (50~60), reference formulas to several sheets (8~10). Master Data file changes (200000 Cells) everyday (Adds/Removes/Re-arranges data). In excel, we just save the file under the same name and it gets overwritten and the data will flow to all linked/referenced documents. But I think smart sheet is ruled by a sheet ID (Not editable) and can be saved with the same file name. Hence, the links still flow from the old outdated master data sheet. How do I either change the sheet link in all report and referenced file in one shot or how do I reference the updated master data file as same as the old master data file.
Best Answer
-
I usually use the API to handle a job like this, but if you are doing it manually, you can upload the new excel using the excel import, delete the contents of the master sheet, then use an automation to copy the rows of the new sheet into the master. The automation may gack when moving that many rows, you’ll want to test it.
Answers
-
I usually use the API to handle a job like this, but if you are doing it manually, you can upload the new excel using the excel import, delete the contents of the master sheet, then use an automation to copy the rows of the new sheet into the master. The automation may gack when moving that many rows, you’ll want to test it.
-
if you want the Python code for the API I can link you to the GitHub
-
I ran into this scenario on a monthly sheet/report where I receive new raw data from supplier in excel format. i created a MASTER sheet with twice as many formula cells indexing my customer data from lookup sheet i refresh often from system files. Monthly I perform a "save as" on the master sheet creating archive file of the previous months data adding the date to the file name of the archived sheet, then deleting all the rows of data in the Master sheet and copy/paste the new month's data from excel into the formula formatted Master smartsheet. This updates all the new data and formulated cells into the original REPORTS derived from the Master sheet with the new month's data. This only works if all of your cells are formula locked (fx). I usually have to hit save after adding the new raw data and refresh the sheet - then all the formulated cells populate without having to touch any of the formulas or update any cells on the pre-existing reports.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives