Help with complicated Index If statement, want to flag a helper row

Rose Howard

Hey friends,

I'm finally figuring out a complex system to have a first sheet with a Dropdown data row, matched to a column with a helper row that only highlights the MAX (most recent) dropdown. I'm doing this so I can ask a dynamic dashboard to retrieve all data on Faculty across multiple sheets.

It works great! Only trouble is, I have a few faculty who 'share' a space, so I have TWO last names in that column.

My formula breaks down, and I'm guessing I need a 'CONTAINS' or 'HAS' but I can't tell where, as the formulas are so long my brain hurts.

The formula I'm using is currently:

=IF([Title of Space / Name of Occupier]@row = "Summary", "", IF([Last Name]@row = INDEX({Faculty Last Name Filter | Last Name}, MATCH(true, {Faculty Last Name Filter | Latest Row}, 0)), 1, IF("ALL" = INDEX({Faculty Last Name Filter | Last Name}, MATCH(true, {Faculty Last Name Filter | Latest Row}, 0)), 1, 0)))

{Faculty Last Name Filter | Last Name} is the separate sheet column with the dropdown options for Faculty Last names.

{Faculty Last Name Filter | Latest Row} is the separate sheet column that is auto flagging a helper flag for most recent Last Name input.

The main sheet with the formula above has a column that is just named Last Name. I'm trying to get the 'Faculty Last Name Helper' column next to it flag if the Last name IS ONE OF the Last names in that column. That way I can list multiple.

It's not working! Help! As you can see, right now it's flagging perfectly for Desjardins, but not for a field that has BOTH Desjardins AND Pepiot.

Kelly Moore

Hey @Rose Howard

I changed the approach to use a COUNTIFS rather than an INDEX/MATCH. I used the [Latest Row] checkbox column as part of the filter for the COUNTIFS.

=IF(COUNTIFS({Faculty Last Name Filter | Last Name}, OR(CONTAINS(@cell, [Last Name]@row),@cell="ALL"), {Faculty Last Name Filter | Latest Row}, 1) > 0, 1)

Does this work for you? I was a bit uncertain on how you were using "ALL".
Kelly

Kelly Moore

Hey @Rose Howard

I changed the approach to use a COUNTIFS rather than an INDEX/MATCH. I used the [Latest Row] checkbox column as part of the filter for the COUNTIFS.

=IF(COUNTIFS({Faculty Last Name Filter | Last Name}, OR(CONTAINS(@cell, [Last Name]@row),@cell="ALL"), {Faculty Last Name Filter | Latest Row}, 1) > 0, 1)

Does this work for you? I was a bit uncertain on how you were using "ALL".
Kelly

Rose Howard

YES @Kelly Moore You aced it! It's working now! Thank you!!!

Kelly Moore

@Rose Howard

Fantastic, I'm glad it works. Have you tested your "ALL" scenario to make sure I understood that correctly?

Rose Howard

I haven't @Kelly Moore, because I took the root file from a template in Smartsheet named 'Dynamic Dashboard with Form Filter' so I didn't build that original formula from scratch, just trusted it. :D