How to change metrics by row for Dashboard

Karla B
Karla B ✭
in Formulas and Functions

Hi there,

I have a sheet that has information for potential projects. Each project has a main row containing info that I have linked up to a dashboard. The idea is that I will pdf the dashboard with the project info, much like a proposal. I would like to make it so that I only have one dashboard and I can change the row that it references rather than have a dashboard for each project.

- I have tried filtering via reports, but the dashboard uses mainly metrics widget so doesn't work with reports.

-I have tried having one row at the top of the sheet that will mirror the required row by entering the row number in another cell. I think this might work as Concatenate in Excel, but the cell references in Smartsheet works differently.

Any suggestions welcome!

Thanks,

Karla

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Karla B

    There currently isn't a way to apply a dynamic filter on a Dashboard in order to customize what data is shown from a sheet.

    Your solution of having one row in a sheet that will bring in the data you want (and then use that row as the row you reference in your Dashboard) is what I would suggest!

    You can use the INDEX function to search through your column and bring back a certain row:

    =INDEX([Column Name]:[Column Name], rownumber)

    What I would do here is have a cell in that top row where you can enter the row number you want to bring back. Then you can reference this cell in your formula, like so:

    =INDEX([Primary Column]:[Primary Column], $[Row to Bring Back]@row)

    Screen Shot 2022-05-06 at 10.04.29 AM.png

    Let me know if this makes sense and will work for you!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Karla B

    There currently isn't a way to apply a dynamic filter on a Dashboard in order to customize what data is shown from a sheet.

    Your solution of having one row in a sheet that will bring in the data you want (and then use that row as the row you reference in your Dashboard) is what I would suggest!

    You can use the INDEX function to search through your column and bring back a certain row:

    =INDEX([Column Name]:[Column Name], rownumber)

    What I would do here is have a cell in that top row where you can enter the row number you want to bring back. Then you can reference this cell in your formula, like so:

    =INDEX([Primary Column]:[Primary Column], $[Row to Bring Back]@row)

    Screen Shot 2022-05-06 at 10.04.29 AM.png

    Let me know if this makes sense and will work for you!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • Karla B
    Karla B ✭

    Great! Thank you Genevieve,

    This suggestion was really useful. It's good to know now that I can return information by row.

    Should this be helpful for others, I adapted the above to Index/Match and added an ID column for the relevant rows (not all rows needed to be returned) so this way, if the row number changed, the data in the reference row didn't.

    Thanks again,

    Karla

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!