Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Community Champion
    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

  • Overachievers Alumni

    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?

    Tomasz Kowalski

    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.

  • Community Champion
    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

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6