How to show "most recent status"

Hi All,

I have a "Client Health" column, where I'm using the five hearts symbols to rate the health of a client from 1 - 5.

The "Client Health" will get updated every 30 days, based on the current status of the account. This happens in rows 19 - 23 only.

In Row 1, of the "Client Health" column, I want to display the most recent "client health" selection, only.

Is there a formula I can use in Row 1 of the "Client Health" column, to say "display only most recent selection from rows 19 - 23, here"?

Thanks in advance!

Client Health Formula screenshot.png

Find more posts tagged with

Sales

Formulas

Comments

Paul Newcome

Yes. If you are just trying to pull the date, you would use something along the lines of

=MAX([Date Column]19:[Date Column]23

If you are trying to pull the text, we would use the MAX formula above to pull a row number for an INDEX function.

=INDEX([Text Column]19:[Text Column]23, MATCH(MAX([Date Column]19:[Date Column]23), [Date Column]19:[Date Column]23, 0))

aspunt1

Hi Paul,

This is so close to working!

Here the INDEX formula I'm using:

=INDEX([Pilot Status]19:[Pilot Status]23, MATCH(MAX([Renewal Date]19:[Renewal Date]23), [Renewal Date]19:[Renewal Date]23, 0))

Issue: the "Pilot Status" cell in row 1 is only populating the value of row 23, only when all rows 19 - 23 are filled in.

Goal: I need the Pilot Status cell in row 1 to populate, with the most recent Pilot Status [from rows 19 - 23], when just row 19 is filled in, and then it will update when row 20 gets filled in, and then update again when row 21 gets filled in, etc...

Paul Newcome

What do you mean when row x gets "filled in"?

Are you meaning that the Pilot Status column has blank rows in it or the date column, or both?

aspunt1

Yes - how do we get the formula to ignore the blank cells in the Pilot Status column? See attached screenshot

Pilot Status example.png

Paul Newcome

So you are wanting to display the number of stars in the same row as the most recent date between rows 19 and 23 that actually has stars populated?

Give this a shot. We included a COLLECT function to only pull dates for the MAX to look at that have rows where the Pilot Status is not blank.

=INDEX([Pilot Status]19:[Pilot Status]23, MATCH(MAX(COLLECT([Renewal Date]19:[Renewal Date]23, [Pilot Status]19:[Pilot Status]23, @cell <> "")), 0))

Sign in to join the conversation!

Quick Links

Trending discussions

Dropdown list restrictions
Is there a way to limit a dropdown list so that the values in the dropdown list can only be used once in a column?
seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet