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

Options

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


«1

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Try this

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

  • Brandon Morales
    Options

    Coming back unparseable

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

  • KPH
    KPH ✭✭✭✭✭✭
    Options


    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
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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
    Options

    no, all populated rows have dates


  • KPH
    KPH ✭✭✭✭✭✭
    Options

    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.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    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
    Options

    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!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!