Help with sheet summary formula losing #REF with data shuttle
I have a dashboard created with a "Data Date" Metric at the top. Using the Created column to populate a "Data Date" column (text column +LEFT(Created@row, 18) I can add the date in the metric via a sheet summary field. When I refresh data pulled from an Excel
sheet (exported by another program), I get a #REF error in the sheet summary field. The "Data Date" column retains the formula and populates as intended. I have the data shuttle replacing all lines in the target sheet, but I am only looking for the date in Row 1, as all rows will be the same. I suspect the data shuttle deletes all the old rows, and that causes the #REF, but how can I constrain it? Or is there another method I can use to populate a Data Date in the dashboard?
Best Answer
-
@Phillip Cullum - You are correct. When Data Shuttle deletes all the existing rows, all references to single cells become #REF errors because at one point in the sheet, there is no data in that top row. There really is not a good way around this. The other possible way to do this would be to use a report rather than a metric widget. Use an Auto Number column (in my scenario called 'Auto'), then a ROW # formula which is =MATCH(Auto@row, Auto:Auto, 0). Then use a report that pulls out that column so long as ROW # is 1. Even as the row numbers will continue to update as the sheet gets wiped, the ROW # column will always match the row number.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
Answers
-
hi @Phillip Cullum,
The #REF error occurs if the column used for formulas is removed at some point.
Are you sure that during your process all the columns are updated only?
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time.
MASA Consult - Your Aligned Smartsheet Gold Partner
Find us on LinkedIn!
Tag my name: @kowal if you need quick response.
-
No, I am not sure. At least on how the functionality of the data shuttle works on the backend at smartsheet. My workflow in the data shuttle is that all target sheet rows are replaced with the data from the excel sheet. The other two options are not viable for us.
I cannot think of another way to populate the data date metric for the dashboard. Any thoughts? I open to any suggestions at this point.
-
@Phillip Cullum - You are correct. When Data Shuttle deletes all the existing rows, all references to single cells become #REF errors because at one point in the sheet, there is no data in that top row. There really is not a good way around this. The other possible way to do this would be to use a report rather than a metric widget. Use an Auto Number column (in my scenario called 'Auto'), then a ROW # formula which is =MATCH(Auto@row, Auto:Auto, 0). Then use a report that pulls out that column so long as ROW # is 1. Even as the row numbers will continue to update as the sheet gets wiped, the ROW # column will always match the row number.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
I can see me getting that to work. Thanks for the thought. It would be nice to ref the smartsheet "Created" column (or other system columns) in sheet summary formulas. We could just use MAX and record the date & time so viewers of the dashboard would know how recent the data pull was.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!