How to show "most recent status"

aspunt1

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! 

 

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 

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))