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?

Find more posts tagged with

Sheet Summary

Data Shuttle

Metric Widget

Accepted answers

Michelle Choate 2

@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.

All comments

kowal

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?

Phillip Cullum

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.

Michelle Choate 2

Phillip Cullum

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.