I need to return the last entered value in a column that has blanks.

ChadMartz_44

Currently have this formula and its not working with blanks in the column.

=INDEX([MO Estimated Hrs]:[MO Estimated Hrs], COUNTIFS([MO Estimated Hrs]:[MO Estimated Hrs], NOT(ISBLANK(@cell))))

The column will have blanks, I want to return the last entered value to the cell for reporting a metric.

Deepak Choudhary

Can you please elaborate a little bit more on the ask, not clear about the problem statement

Andrée Starå

Hi @ChadMartz_44

I hope you're well and safe!

You'd probably need something like an INDEX/COLLECT formula.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/collect

Do you have an Autonumber or Created System Column in the sheet already?

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ChadMartz_44

I'm looking to return the last entered value in a column to a reporting cell. Although the Column will contain blank cells periodically.

I've tried this. I feel like I'm getting close with no good result.

Andrée Starå

@ChadMartz_44

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

ChadMartz_44

@Andrée Starå I would like to report the last entered value to the sheet summary.

Andrée Starå

@ChadMartz_44

Try adding the System Column Created Date, and then try something like this.

=INDEX([MO Number]:[MO Number], MATCH(MAX(Created:Created), Created:Created))

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ChadMartz_44

@Andrée Starå yes that works. However the only problem I'm facing is if the Operator Communication is entered from the form, then it comes back blank.

I would like the operator communication to be entered without all the other data, when they are reffering to the pervious MO Number.

I hope this makes sense.

Andrée Starå

@ChadMartz_44

Not sure I follow!

Can you elaborate on how you'd like it to work for the different scenarios?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ChadMartz_44

@Andrée Starå There is going to be blanks in the column occasionally. I want to report the last entered value of the column, to the reporting cell or sheet summary field.

So the entry might be at a later date and time.

Andrée Starå

@ChadMartz_44

So you want to show the latest value that isn't blank (referencing the Created column). Correct?

ChadMartz_44

@Andrée Starå

It doesn't need to reference the created date as long as I can retrieve the last entered value.

Andrée Starå

@ChadMartz_44

Would the last entered value always be higher?

ChadMartz_44

@Andrée Starå

For the MO Number yes. However, I look to do this with some of the other columns and those numbers can be higher or lower.

Andrée Starå

@ChadMartz_44

Try this.

=MAX(COLLECT([MO Number]:[MO Number], Created:Created, <>"", [MO Number]:[MO Number], <>""))

Did it work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ChadMartz_44

@Andrée Starå

It started working. Although when the last entry was entered it only returned the value before it.

Current MO Order Qty should've returned 110, but returned 1400. Same for the MO Estimated Hrs. Could this have to do something with the created date?