Report to show last data entry only

Lee Wood

Good morning,

I am looking for a report to return a column value from the last sheet data entry.

Essentially, I would like to create a dashboard that tells me whether my machine is online or offline based on the data that the operator puts into the highlighted column. I'm guessing that generating a report would be the best way, but I'm not too sure how to get it to return the last value within this column only.

Thanks for your help in advance.

Genevieve P.

Hi @Lee Wood

I would actually suggest using a formula instead, then you can use a Metric Widget to look at that cell and return the most recent value.

The way I would do this is to use a Sheet Summary field to house the formula. Then make sure you have a System Column in your sheet that's tracking the Created Date.

Your formula can search the Created column for the MAX date (most recent date) and return the value from the Online or Offline column, like so:

=INDEX([Online or Offline]:[Online or Offline], MATCH(MAX(Created:Created), Created:Created))

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

Cheers,

Genevieve

Lee Wood

Hi @Genevieve P.

Thank you for helping, the formula works perfectly. Out of interest, is there a way of returning the last data entry for that column (Online or Offline) that was not blank? The reason I ask is that there is occassion when certain cells won't be populated within a data entry so I would like to display the final filled cell data.

Thanks

Lee

Genevieve P.

Hi @Lee Wood

Yes! The way I would do this is to change it to an INDEX(COLLECT, then add a MAX(COLLECT for the MAX criteria, like so:

=INDEX(COLLECT([Online or Offline]:[Online or Offline], Created:Created, MAX(COLLECT(Created:Created, [Online or Offline]:[Online or Offline], <>""))), 1)

ChadMartz_44

Hi @Lee Wood

https://community.smartsheet.com/discussion/comment/310242#Comment_310242

This formula works well for a metric sheet I'm building, I just ran into one snag. How can I only pull it if it's a number and not text?

I get #divide by zero, when I want 50.

Genevieve P.

Hi @ChadMartz_44

Can you clarify what column or reference contains both numbers and text, and what you want to bring back? It would be helpful to see the full formula, as well as a screen capture of the sheet you're referencing (but block out sensitive data).

Thanks!

Genevieve

ChadMartz_44

@Genevieve P.

I'm trying to report the Lbs/Hr Goal. Which just contains the MO Order Qty/MO Est Hrs. I want to report the most recent valve to the sheet summary for reporting the metric to a dashboard. Your formula is working for all of them but, the column's I have the column formula in.

If I can figure this out, I should be good to finish the dashboards. I appreciate your help!

Genevieve P.

Hi @ChadMartz_44

The reason you're seeing an error is because the columns you're referencing contain an error in one cell. Even though that's not the cell that should be brought back, the formula gets stuck as there's an error in the column.

Try wrapping an IFERROR statement around your Column Formulas in the sheet:

=IFERROR(formula, "")

This will return a blank cell instead of an error. Once your columns are sorted out, your Sheet Summary panel formulas should work properly. See: IFERROR Function

Let me know if that worked for you!

Cheers,

Genevieve

ChadMartz_44

Perfect thanks!!!