What function can recognize a name in a multidrop down list and reference a corresponding column?

Jordan Myers

I am trying to create a sheet with the purpose of allowing a supervisor to see what crew members are currently busy and what days. This is Sheet B, while Sheet A is the list of projects and all corresponding details, like dates and crew members.

I am doing this with an "Status" column in Sheet B, which scans the crews of Sheet A and finds any assigned members. If it finds a crew member, it tags their corresponding "Status" column with an "ACTIVE". If this column has an "ACTIVE" in it, another "Projected Start Date" column activates and collects a corresponding date for the corresponding employee. It does this by referring to a helper column in Sheet B, which is referring to Sheet A for the proper row number for the employee. This number is then used in an INDEX function to grab the proper date for the start and end dates.

The issue occurs when the helper column in Sheet B has to scan for one name in a multi list of more than one. I think the issue is that it is recognizing the list as a whole piece of a data. It is like searching for the letter "A" and "A" alone in a list of "A, B, C" and it only seeing "ABC" despite the letters being different selections. How would I set it up so that it can recognize that this row contains (Employee Name), even though there are other names, and reference to an corresponding column, namely the start and end dates?

Thank you in advance

Genevieve P.

Hi @Jordan Myers

My apologies for not being clear, the COUNTIF was an example to show how the HAS would be formatted, not as an example of your specific formula. This is great additional information though, thank you!

So in your instance, the criteria you need to wrap the HAS around is the [Employee Name]@row value.

Try this instead:

=SUMIF({Low bid to Construction Range 1}, HAS(@cell, [Employee Name]@row), {Low bid to Construction Range 2})

Bassam Khalil

Hi @Jordan Myers

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Genevieve P.

Hi @Jordan Myers

When you're searching for one selection in a multi-select cell (contact or dropdown list), you'll want to use the HAS function (see here) to search for that value.

What is the formula you're currently using? Where you have the criteria/user that you're searching for, wrap this criteria in the HAS function.

For example, instead of:

=COUNTIF({Multi Column}, "Value")

You would do:

=COUNTIF({Multi Column}, HAS(@cell, "Value"))

Let me know if this works for you! If not, it would be helpful to see your current formula and two screen captures (one of each sheet set up, but please block out sensitive data).

Cheers,

Genevieve

Jordan Myers

Sorry I am responding so late.

I am not using =COUNTIF functions right now. My current solution is to use a helper column in sheet A to basically create a column for row numbers. This helper column values are identified by using =SUM(INDEX$1:INDEX1) and referencing a second helper column called "INDEX" where every cell value is "1". I use this "row" number in a INDEX function in sheet B, since I need to have a row value in order for the function to work. I did this so I do not have to manually enter which row a crew member is in.

My issue is getting a function to recognize that a name is contained in a list and to reference the above mentioned helper column for the row number. I know it works as it works when there is only one name in a cell.

The formula I use to recognize the name and find the corresponding row number is =SUMIF({Low bid to Construction Range 1}, [Employee Name]3, {Low bid to Construction Range 2}). The first range is the entire column of active employees in sheet A and the second range is the search for the proper row number in sheet A. It uses the employee name to grab the proper row after identifying that the name is in sheet A.

The first photo is of Sheet B and the second photo is of the crew members column in Sheet A.

Thank you very much.

Genevieve P.

Hi @Jordan Myers

My apologies for not being clear, the COUNTIF was an example to show how the HAS would be formatted, not as an example of your specific formula. This is great additional information though, thank you!

So in your instance, the criteria you need to wrap the HAS around is the [Employee Name]@row value.

Try this instead:

=SUMIF({Low bid to Construction Range 1}, HAS(@cell, [Employee Name]@row), {Low bid to Construction Range 2})

Jordan Myers

Thank you very much for your help. It worked out.

Genevieve P.

No problem! 🙂