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!
Comments
-
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))
-
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...
-
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?
-
Yes - how do we get the formula to ignore the blank cells in the Pilot Status column? See attached screenshot
-
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))
Help Article Resources
Categories
Check out the Formula Handbook template!
