Conversation Formula HELP!! in Excel
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/508345#M30101
<P>Would welcome some help with the following problem....</P><P> </P><P>One of my colleagues maintains a planner for a group of volunteers - similar to below</P><P>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</P><P> </P><TABLE><TBODY><TR><TD> </TD><TD>Last Date Worked</TD><TD> </TD><TD>01/05/2019</TD><TD>02/05/2019</TD><TD>03/05/2019</TD><TD>04/05/2019</TD><TD>05/05/2019</TD><TD>06/05/2019</TD><TD>07/05/2019</TD></TR><TR><TD>Jon</TD><TD>07/05/19</TD><TD> </TD><TD>x</TD><TD>x</TD><TD>x</TD><TD> </TD><TD> </TD><TD> </TD><TD>x</TD></TR><TR><TD>Steve</TD><TD>04/05/19</TD><TD> </TD><TD> </TD><TD>x</TD><TD> </TD><TD>x</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Adrian</TD><TD>05/05/19</TD><TD> </TD><TD> </TD><TD> </TD><TD>x</TD><TD> </TD><TD>x</TD><TD> </TD><TD> </TD></TR><TR><TD>JonS</TD><TD>07/05/19</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>x</TD><TD>x</TD></TR></TBODY></TABLE>Wed, 01 May 2019 15:49:42 GMTJonWells2019-05-01T15:49:42ZFormula HELP!!
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/508345#M30101
<P>Would welcome some help with the following problem....</P><P> </P><P>One of my colleagues maintains a planner for a group of volunteers - similar to below</P><P>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</P><P> </P><TABLE><TBODY><TR><TD> </TD><TD>Last Date Worked</TD><TD> </TD><TD>01/05/2019</TD><TD>02/05/2019</TD><TD>03/05/2019</TD><TD>04/05/2019</TD><TD>05/05/2019</TD><TD>06/05/2019</TD><TD>07/05/2019</TD></TR><TR><TD>Jon</TD><TD>07/05/19</TD><TD> </TD><TD>x</TD><TD>x</TD><TD>x</TD><TD> </TD><TD> </TD><TD> </TD><TD>x</TD></TR><TR><TD>Steve</TD><TD>04/05/19</TD><TD> </TD><TD> </TD><TD>x</TD><TD> </TD><TD>x</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Adrian</TD><TD>05/05/19</TD><TD> </TD><TD> </TD><TD> </TD><TD>x</TD><TD> </TD><TD>x</TD><TD> </TD><TD> </TD></TR><TR><TD>JonS</TD><TD>07/05/19</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>x</TD><TD>x</TD></TR></TBODY></TABLE>Wed, 01 May 2019 15:49:42 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help/m-p/508345#M30101JonWells2019-05-01T15:49:42ZRe: Formula HELP!!
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/508401#M30104
<P><LI-USER uid="331302"></LI-USER> </P><P> </P><PRE>=LOOKUP(PI(),1/(D2:J2="x"),D$1:J$1)</PRE><P> </P>Wed, 01 May 2019 16:09:12 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help/m-p/508401#M30104Detlef Lewin2019-05-01T16:09:12ZRe: Formula HELP!!
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/508714#M30115
<P><LI-USER uid="331302"></LI-USER> </P><P> </P><P>LOOKUP has its elegance but MAXIFS also offers a way forward.</P><P>= IF( COUNTA(DaysWorked), MAXIFS( Dates, DaysWorked, "x" ), "None recorded" )</P><P>where 'DaysWorked' is a Name referencing a single relative row of the table.</P><P> </P><TABLE><TBODY><TR><TD>Last Date Worked</TD><TD> </TD><TD>01/05/2019</TD><TD>02/05/2019</TD><TD>03/05/2019</TD><TD>04/05/2019</TD><TD>05/05/2019</TD><TD>06/05/2019</TD><TD>07/05/2019</TD></TR><TR><TD>07/05/2019</TD><TD>07/05/2019</TD><TD>x</TD><TD>x</TD><TD>x</TD><TD> </TD><TD> </TD><TD> </TD><TD>x</TD></TR><TR><TD>04/05/2019</TD><TD>04/05/2019</TD><TD> </TD><TD>x</TD><TD> </TD><TD>x</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>05/05/2019</TD><TD>05/05/2019</TD><TD> </TD><TD> </TD><TD>x</TD><TD> </TD><TD>x</TD><TD> </TD><TD> </TD></TR><TR><TD>07/05/2019</TD><TD>None recorded</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>Wed, 01 May 2019 17:53:52 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help/m-p/508714#M30115Peter Bartholomew2019-05-01T17:53:52ZRe: Formula HELP!!
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/509035#M30126
There you go again with your sexy PI(). I love it as much as you do.Wed, 01 May 2019 19:32:18 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help/m-p/509035#M30126Twifoo2019-05-01T19:32:18ZRe: Formula HELP!!
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/509209#M30130
<P><LI-USER uid="280482"></LI-USER> </P><P>Since I never use direct cell references, I can always take the formula a step further and write</P><P>= LOOKUP( π, 1/(DaysWorked="x"), Dates )</P><P>It all depends on which pi(e) one considers the most tasty :-)</P><P> </P><P>I like '<SPAN>π' [ refers to =PI() ] because most documentation says it is not a legal name -- but it works.</SPAN></P><P><SPAN>It is just a tad difficult to type, though.</SPAN></P>Wed, 01 May 2019 20:11:24 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help/m-p/509209#M30130Peter Bartholomew2019-05-01T20:11:24ZRe: Formula HELP!!
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/510751#M30157
<P>Thank you so much - worked a treat! <LI-USER uid="1639"></LI-USER> </P>Thu, 02 May 2019 06:43:39 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help/m-p/510751#M30157JonWells2019-05-02T06:43:39ZRe: Formula HELP!!
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/510753#M30158
<P>Sincere thanks <LI-USER uid="214174"></LI-USER> - will pass both options to my colleague and let them choose which to use. Cant thank the community enough for such a timely response!</P>Thu, 02 May 2019 06:45:53 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help/m-p/510753#M30158JonWells2019-05-02T06:45:53ZRe: Formula HELP!!
https://techcommunity.microsoft.com/t5/excel/formula-help/m-p/511230#M30162
<P>Hi <LI-USER uid="331302"></LI-USER> </P><P>If your colleagues simply uses 1 instead of 'x' then he can also use below formula to achieve desire result.</P><P>'=MAX($D$1:$J$1*D2:J2) press Ctrl+Shift+Enter</P><P>Regards</P><P> </P><P> </P>Thu, 02 May 2019 09:56:20 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help/m-p/511230#M30162tauqeeracma2019-05-02T09:56:20Z