• 667K Members
• 9,999 Online
• 822K Conversations

Highlighted
New Contributor

# Formula HELP!!

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
7 Replies
Highlighted

# Re: Formula HELP!!

`=LOOKUP(PI(),1/(D2:J2="x"),D\$1:J\$1)`

Highlighted

# Re: Formula HELP!!

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
Highlighted

# Re: Formula HELP!!

There you go again with your sexy PI(). I love it as much as you do.
Highlighted

# Re: Formula HELP!!

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.

Highlighted

# Re: Formula HELP!!

Thank you so much - worked a treat! @Detlef Lewin

Highlighted

# Re: Formula HELP!!

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!

Highlighted

# Re: Formula HELP!!

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