Using most recently added row to populate dashboard

kirstymclean

Hi - I'm building a dashboard for project reporting, and need the most recently updated row from my data sheet to be used to populate the Metric Widgets. The data needs to come from a sheet rather than a report as I want to display the data split out into areas, not all in one place.

I've done a lot of reading, and so far I've:

• Added the 'Created' date/time column
• Added a helper column with a checkbox, which ticks the most recently updated row with the formula =IF(Created@row = MAX(Created:Created), 1)

What I'm now stuck on is how to reference just the ticked row in the dashboard. I suspect I need to add a summary sheet, or a helper sheet, but I can't find any clear instructions of how to pull just the data from the row with the checkbox ticked.

If anyone would be able to advise on the best way to achieve this I would be very grateful!

Thanks,

Kirsty

Kelly Moore

try this

=INDEX([Quote Amount]:[Quote Amount], MATCH(true, [Helper]:[Helper], 0))

Kelly

Kelly Moore

Hey @kirstymclean

Yes, you can construct a 'metrics sheet'. To do so, you'll use cross sheet formulas. Are you familiar with building those? The syntax for the range designation is slightly different than when using same-sheet columns.

=INDEX({Source sheet Quote Amount column}, MATCH(true, {Source sheet Helper column}, 0))

Because this does contain cross sheet references you will have to manually insert these references in your sheet through the formula window. You cannot simply copy paste the above into your sheet.

Let me know if you need any help with cross sheet formulas.

Kelly

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

Kelly Moore

Hey @kirstymclean

You have a couple of approaches that will work, since you've already done all the heavy lifting by designating the row you need.

If you want the row displayed on your dashboard, a simple row report will do the job for you. You will filter the report using the Max Helper column you created.

If it's one or two values in the row that you need, you could add a Summary field(s) to your sheet (found in the RIGHT hand ribbon on your sheet), and either add the value(s) as metric widgets or, you could build a Summary field report and bring them in that way. It depends how you are designing the layout of your dashboard.

If you need specific instructions on either of these approaches, I'd be happy to provide more details. Just shout out.

Kelly

kirstymclean

Hi @Kelly Moore

Thanks so much for your response.

A report won't work due to the way I need to display the data in the dashboard - I need to be able to use Metric Widgets.

So I think this solution will work best - "If it's one or two values in the row that you need, you could add a Summary field(s) to your sheet (found in the RIGHT hand ribbon on your sheet), and either add the value(s) as metric widgets"

Are you able to help me with a formula to use in the summary fields?

So for example - I have a column named Quote Amount. I'd like to add a summary field which will pull the data from this cell IF the checkbox in the Helper column is ticked.

I've tried =INDEX([Quote Amount], MATCH(1, [Helper], 0)) and other variations on this, but keep getting the #UNPARSEABLE result.

Thanks!

Kirsty

Kelly Moore

Hey Kirsty

Your formula just needs a slight adjustment in how you have your ranges expressed. The syntax I used below is how to reference an entire column (that is not a cross sheet column).

=INDEX([Quote Amount]:[Quote Amount], MATCH(1, [Helper]:[Helper], 0))

If you have any trouble bringing Summary Field references into a metric's widget, let me know.

Kelly

kirstymclean

Thanks @Kelly Moore

So, some improvement with your changes to the formula, see below:

However, it's returning #NO MATCH when clearly there is a checked box in the Helper column.

Any ideas?

Thanks so much for being so helpful 😅

Kelly Moore

try this

=INDEX([Quote Amount]:[Quote Amount], MATCH(true, [Helper]:[Helper], 0))

Kelly

kirstymclean

That's done it 🎉

Thanks so much @Kelly Moore, really appreciate your help today.

kirstymclean

@Kelly Moore I'm wondering if you can advise on another issue I've found now this is working...

For my dashboard, I need the data in the Metric Widgets to mirror the sheet formatting, so for example if the predicted budget column figure is higher than the original budget column figure, it should show as red.

However, now I'm pulling the data from the summary sheet, the conditional formatting no longer works 🙈 I've looked into this and I can see this functionality has been requested, but not currently possible.

Is there a better/different way of achieving what I need here? Could I potentially auto-mirror just the ticked row to another sheet, which I can then use to populate my dashboard? Or mirror the summary sheet data to another sheet? Or use an automation?

Any advice you could give would be very gratefully received!

Kelly Moore

Hey @kirstymclean

Yes, you can construct a 'metrics sheet'. To do so, you'll use cross sheet formulas. Are you familiar with building those? The syntax for the range designation is slightly different than when using same-sheet columns.

=INDEX({Source sheet Quote Amount column}, MATCH(true, {Source sheet Helper column}, 0))

Because this does contain cross sheet references you will have to manually insert these references in your sheet through the formula window. You cannot simply copy paste the above into your sheet.

Let me know if you need any help with cross sheet formulas.

Kelly

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

kirstymclean

Thank you again @Kelly Moore - all sorted and my dashboard is working perfectly!