May 01 2019 08:49 AM
Would welcome some help with the following problem....
One of my colleagues maintains a planner for a group of volunteers - similar to below
I'm trying to populate the 'Last Date Worked' cell with the most recent entry. They currently have a spreadsheet with a column for each day and also colour code cells but for the example below, i've tried to keep it simple. Cells can either be blank or populated with an x. Where there is a 'x', they want to retain these so they can see who's been working and when, but would like column B (Last Date Worked) to be populated with the most recent date where cells have an x - any suggestions on how to achieve this? Thanks in advance, Jon
Last Date Worked | 01/05/2019 | 02/05/2019 | 03/05/2019 | 04/05/2019 | 05/05/2019 | 06/05/2019 | 07/05/2019 | ||
Jon | 07/05/19 | x | x | x | x | ||||
Steve | 04/05/19 | x | x | ||||||
Adrian | 05/05/19 | x | x | ||||||
JonS | 07/05/19 | x | x |
May 01 2019 10:53 AM
LOOKUP has its elegance but MAXIFS also offers a way forward.
= IF( COUNTA(DaysWorked), MAXIFS( Dates, DaysWorked, "x" ), "None recorded" )
where 'DaysWorked' is a Name referencing a single relative row of the table.
Last Date Worked | 01/05/2019 | 02/05/2019 | 03/05/2019 | 04/05/2019 | 05/05/2019 | 06/05/2019 | 07/05/2019 | |
07/05/2019 | 07/05/2019 | x | x | x | x | |||
04/05/2019 | 04/05/2019 | x | x | |||||
05/05/2019 | 05/05/2019 | x | x | |||||
07/05/2019 | None recorded |
May 01 2019 12:32 PM
May 01 2019 01:11 PM
Since I never use direct cell references, I can always take the formula a step further and write
= LOOKUP( π, 1/(DaysWorked="x"), Dates )
It all depends on which pi(e) one considers the most tasty :)
I like 'π' [ refers to =PI() ] because most documentation says it is not a legal name -- but it works.
It is just a tad difficult to type, though.
May 01 2019 11:45 PM
Sincere thanks @Peter Bartholomew - will pass both options to my colleague and let them choose which to use. Cant thank the community enough for such a timely response!
May 02 2019 02:56 AM
Hi @JonWells
If your colleagues simply uses 1 instead of 'x' then he can also use below formula to achieve desire result.
'=MAX($D$1:$J$1*D2:J2) press Ctrl+Shift+Enter
Regards