Home

Formula HELP!!

%3CLINGO-SUB%20id%3D%22lingo-sub-508345%22%20slang%3D%22en-US%22%3EFormula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508345%22%20slang%3D%22en-US%22%3E%3CP%3EWould%20welcome%20some%20help%20with%20the%20following%20problem....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20my%20colleagues%20maintains%20a%20planner%20for%20a%20group%20of%20volunteers%20-%20similar%20to%20below%3C%2FP%3E%3CP%3EI'm%20trying%20to%20populate%20the%20'Last%20Date%20Worked'%20cell%20with%20the%20most%20recent%20entry.%26nbsp%3B%20%26nbsp%3BThey%20currently%20have%20a%20spreadsheet%20with%20a%20column%20for%20each%20day%20and%20also%20colour%20code%20cells%20but%20for%20the%20example%20below%2C%20i've%20tried%20to%20keep%20it%20simple.%26nbsp%3B%26nbsp%3BCells%20can%20either%20be%20blank%20or%20populated%20with%20an%20x.%26nbsp%3B%20Where%20there%20is%20a%20'x'%2C%20they%20want%20to%20retain%20these%20so%20they%20can%20see%20who's%20been%20working%20and%20when%2C%20but%20would%20like%20column%20B%20(Last%20Date%20Worked)%20to%20be%20populated%20with%20the%20most%20recent%20date%20where%20cells%20have%20an%20x%20-%20any%20suggestions%20on%20how%20to%20achieve%20this%3F%26nbsp%3B%20Thanks%20in%20advance%2C%20Jon%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ELast%20Date%20Worked%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E01%2F05%2F2019%3C%2FTD%3E%3CTD%3E02%2F05%2F2019%3C%2FTD%3E%3CTD%3E03%2F05%2F2019%3C%2FTD%3E%3CTD%3E04%2F05%2F2019%3C%2FTD%3E%3CTD%3E05%2F05%2F2019%3C%2FTD%3E%3CTD%3E06%2F05%2F2019%3C%2FTD%3E%3CTD%3E07%2F05%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJon%3C%2FTD%3E%3CTD%3E07%2F05%2F19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESteve%3C%2FTD%3E%3CTD%3E04%2F05%2F19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAdrian%3C%2FTD%3E%3CTD%3E05%2F05%2F19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJonS%3C%2FTD%3E%3CTD%3E07%2F05%2F19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-508345%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508401%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331302%22%20target%3D%22_blank%22%3E%40JonWells%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DLOOKUP(PI()%2C1%2F(D2%3AJ2%3D%22x%22)%2CD%241%3AJ%241)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508714%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331302%22%20target%3D%22_blank%22%3E%40JonWells%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELOOKUP%20has%20its%20elegance%20but%20MAXIFS%20also%20offers%20a%20way%20forward.%3C%2FP%3E%3CP%3E%3D%20IF(%20COUNTA(DaysWorked)%2C%20MAXIFS(%20Dates%2C%20DaysWorked%2C%20%22x%22%20)%2C%20%22None%20recorded%22%20)%3C%2FP%3E%3CP%3Ewhere%20'DaysWorked'%20is%20a%20Name%20referencing%20a%20single%20relative%20row%20of%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ELast%20Date%20Worked%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E01%2F05%2F2019%3C%2FTD%3E%3CTD%3E02%2F05%2F2019%3C%2FTD%3E%3CTD%3E03%2F05%2F2019%3C%2FTD%3E%3CTD%3E04%2F05%2F2019%3C%2FTD%3E%3CTD%3E05%2F05%2F2019%3C%2FTD%3E%3CTD%3E06%2F05%2F2019%3C%2FTD%3E%3CTD%3E07%2F05%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E07%2F05%2F2019%3C%2FTD%3E%3CTD%3E07%2F05%2F2019%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04%2F05%2F2019%3C%2FTD%3E%3CTD%3E04%2F05%2F2019%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E05%2F05%2F2019%3C%2FTD%3E%3CTD%3E05%2F05%2F2019%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E07%2F05%2F2019%3C%2FTD%3E%3CTD%3ENone%20recorded%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-509035%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-509035%22%20slang%3D%22en-US%22%3EThere%20you%20go%20again%20with%20your%20sexy%20PI().%20I%20love%20it%20as%20much%20as%20you%20do.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-509209%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-509209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20I%20never%20use%20direct%20cell%20references%2C%20I%20can%20always%20take%20the%20formula%20a%20step%20further%20and%20write%3C%2FP%3E%3CP%3E%3D%20LOOKUP(%20%CF%80%2C%201%2F(DaysWorked%3D%22x%22)%2C%20Dates%20)%3C%2FP%3E%3CP%3EIt%20all%20depends%20on%20which%20pi(e)%20one%20considers%20the%20most%20tasty%20%3A-)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20like%20'%3CSPAN%3E%CF%80'%20%5B%20refers%20to%20%3DPI()%20%5D%20because%20most%20documentation%20says%20it%20is%20not%20a%20legal%20name%20--%20but%20it%20works.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIt%20is%20just%20a%20tad%20difficult%20to%20type%2C%20though.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510751%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510751%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20-%20worked%20a%20treat!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510753%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510753%22%20slang%3D%22en-US%22%3E%3CP%3ESincere%20thanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20-%20will%20pass%20both%20options%20to%20my%20colleague%20and%20let%20them%20choose%20which%20to%20use.%26nbsp%3B%20%26nbsp%3BCant%20thank%20the%20community%20enough%20for%20such%20a%20timely%20response!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-511230%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-511230%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331302%22%20target%3D%22_blank%22%3E%40JonWells%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20colleagues%20simply%20uses%201%20instead%20of%20'x'%20then%20he%20can%20also%20use%20below%20formula%20to%20achieve%20desire%20result.%3C%2FP%3E%3CP%3E'%3DMAX(%24D%241%3A%24J%241*D2%3AJ2)%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20press%20Ctrl%2BShift%2BEnter%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
JonWells
New Contributor

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/201902/05/201903/05/201904/05/201905/05/201906/05/201907/05/2019
Jon07/05/19 xxx   x
Steve04/05/19  x x   
Adrian05/05/19   x x  
JonS07/05/19      xx
7 Replies

@JonWells 

 

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

 

@JonWells 

 

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/201902/05/201903/05/201904/05/201905/05/201906/05/201907/05/2019
07/05/201907/05/2019xxx   x
04/05/201904/05/2019 x x   
05/05/201905/05/2019  x x  
07/05/2019None recorded       
There you go again with your sexy PI(). I love it as much as you do.

@Twifoo 

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.

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

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!

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

 

 

Related Conversations