How do you have multiple cells populate when drop down column is used?

Stephen Carroll

First of all, not sure if this doable, but I'm sure it has to be.

I have 2 sheets. On the first sheet, we have a column in which multiple people can be checked it looks like this:

We then have a second sheet in which we use index match formulas to populate the address with the appropriate name. What happens when we select more than name on the first sheet is that on the second sheet, only the first name appears.

Here is the formula we are using:

=IFERROR(IF(Name1 = "", "", INDEX({Work Site}, MATCH(Name1, {Test Sheet Range 2}, 1))), "")

(Not all rows will have a name under the match thus the iferror so it doesn't show up as "#NO MATCH")

This seems to work flawlessly if its only 1 person selected but not multiple.

Is this possible or will we just have to have multiple rows for the same address with singular selection on Sheet 1? Thanks in advance for your help.

Genevieve P.

Hi Stephen,

Could the people be selected in multiple cells in the first sheet? If not, you could use an INDEX(COLLECT instead.Then you could have a HAS function to see if the Multi-Select column has a specific person. Try this:

=INDEX(COLLECT({Work Site}, {Test Sheet Range 2}, HAS(@cell, Name@row)), 1)

Then in your full formula:

=IFERROR(IF(Name@row = "", "", INDEX(COLLECT({Work Site}, {Test Sheet Range 2}, HAS(@cell, Name@row)), 1), "")

Let me know if this works for you!

Cheers,

Genevieve

Stephen Carroll

Multiple cells across columns but should not (and would love a way to reduce options for later in the column but that's a whole other question).

Its for the creation of a weekly schedule.

So if Person A is on Site 1 on Monday they won't be selected on any other site in the Monday Column but they could be selected for any site in the Tuesday column.

I built it so that each day only references and matches the data for that column.

I'll have to try your formula and see how it works since we have each day looking up at the appropriate day on the other sheet.

Once I get time to test it out, i'll let you know if it works in our situation.

Thanks!

Genevieve P.

Ok great, that makes a lot of sense! The formula above should work, then.

Since you're not actually adjusting any of the ranges or changing up what columns you're looking at, but you're changing the syntax and the functions in the formula, you can copy/paste the range names without needing to select them again in the pop-up window. Hope that helps!