Hi, is there away to find the MAX date on a sheet based on criteria?

Brandon Morales

For example, I would like to find the most recent date below if the column, RGH/MLD/CCT MACHINE/EQUIPMENT, has the entry: P1500

KPH

Try this

=MAX(COLLECT([Last Maintenance Date]:[Last Maintenance Date], [RGH/MLD/CCT MACHINE/EQUIPMENT]:[RGH/MLD/CCT MACHINE/EQUIPMENT], "P1500"))

Brandon Morales

Coming back unparseable

range 4 is my date only column and range 5 is my RGH/MLD/CCT MACHINE/EQUIPMENT column.

KPH

You have a problem with your cross sheet references. If you are referencing a column within the current sheet you use the column name with the colon in the middle. If you are cross referencing another sheet you must select the entire column, give it a name and just reference that.

Taking my original formula I highlight the column that is in a difference sheet and select the Reference Another Sheet Option

This opens a new dialogue box and I select the entire column and name it. I used "Maint Date". I think you called this SCHEDULED MAINTENANCE Range 4

I then select the other column reference and click Reference Another Sheet. This time I select the other column and call this RGH. I think you called this SCHEDULED MAINTENANCE Range 4

My resulting formula is

=MAX(COLLECT({Maint Date}, {RGH}, "P1500"))

Assuming you selected the same columns, and named them as I assumed, your formula could be

=MAX(COLLECT({SCHEDULED MAINTENANCE Range 4},{SCHEDULED MAINTENANCE Range 5},"P1500"))

Brandon Morales

Ok, this really helps and I think I am very close. Just one problem, my column, RGH/MLD/CCT MACHINE/EQUIPMENT column needs to allow multiple values per cell. this means that using the formula above, it will only recognize dates that with specific criteria of "P1500" only. If the cell has multiple values it doesnt work...

KPH

Oh sorry @Brandon Morales I couldn't see your screen shot yesterday so didn't notice you had a dropdown.

One idea is to add (and hide) a helper column with the formula

=FIND("P1500", [RGH/MLD/CCT MACHINE/EQUIPMENT]@row)

This will put a "1" in the helper column where "P1500" is in the RGH... cell. Like this...

Then change your MAX COLLECT to pull in the max date where Helper is "1" rather than where RGH etc. is "P1500"

So if this wasn't across sheets it would be

=MAX(COLLECT([Last Maintenance Date]:[Last Maintenance Date], Helper:Helper, "1"))

If you are doing this across sheets the formula would look something like this (depending on what you call the columns when you reference them)

=MAX(COLLECT({Maint Date}, {Helper}, "1"))

Brandon Morales

Perfect. That will work. Thanks!

I have one more question... i hope...

when i try to add the additional criteria of required frequency it does not seem to work properly. in the image below I add the range of required frequency and criteria of DAILY / WEEKLY. This means that for U2300, DAILY / WEEKLY, I should only find 11/1/23 as my max date but for some reason im getting 11/2/23.

KPH

Your pink parenthesis is in the wrong place. Move it to the end, next to the blue one.

=MAX(COLLECT([Last Maintenance Date]:[Last Maintenance Date], Helper:Helper, "1", [REQUIRED FREQUENCY]:[REQUIRED FREQUENCY], "DAILY / WEEKLY"))

Brandon Morales

the formula you describe outputs a blank cell- Unless i still have syntax off...

below is the sheet it references with range 3 as date only, range 5 as rgh/mld/cct/machine/equipment (this is now limited to one value), and range 1 as required frequency.

I would expect my formula to retur this date: 11/1/23

KPH

Formula looks good to me. Can you check there isn't a row off the bottom of the screen with some data in it and no date?

Brandon Morales

no, all populated rows have dates

KPH

Would you be able to export the big sheet as an excel file? I'll import it and see if I can reproduce the issue? I can't see anything obvious.

Brandon Morales

Machine Maintenance Instructions.xlsx

attached here is the big sheet.

Brandon Morales

SCHEDULED MAINTENANCE.xlsx

here is the smaller file.

KPH

Hi Brandon

I imported your sheet and set up cross sheet references using the same names as you provided. The formula works fine for me. Here are some examples, if you want to copy and paste to make sure there isn't a comma or quote missing.

=MAX(COLLECT({SCHEDULED MAINTENANCE Range 3}, {SCHEDULED MAINTENANCE Range 5}, "P1500", {SCHEDULED MAINTENANCE Range 1}, "WEEKLY"))

and

=MAX(COLLECT({SCHEDULED MAINTENANCE Range 3}, {SCHEDULED MAINTENANCE Range 5}, "U2300", {SCHEDULED MAINTENANCE Range 1}, "WEEKLY"))

The only thing I noticed is that your SCHEDULED MAINTENANCE sheet had "DAILY / WEEKLY" as a value in your screen shots but WEEKLY in the sheet you attached. There are no rows with DAILY / WEEKLY.

Brandon Morales

SCHEDULED MAINTENANCE.xlsx

I made a change to daily and weekly and separated the two. but i deleted two brackets that were un- needed and it now works!! thanks for all your help!