Complex multiple worksheet formula - Is this possible?

[SNyansa]

Hello all,

I have a bit of a challenge here if anyone can help. I'm not entirely sure if it's even possible but I'm sure I've seen someone do something similar on a YouTube video but I may be mistaken.

I want a formula (or formulas) in the range B8:O19, which will pull back information from specific worksheets based on the selection in cell B4. This formula would have to bring back corresponding 'Options' chosen for each 'Employee' depending on whether there is a tick in that field, this can all be returned in one cell if possible. I have done an example of how I would want it to look. If it is not possible the way I have drawn, I would be happy to entertain alternatives. I'm just keen not to rewrite the entire row of Options, and rather have the chosen ones show up individually.

Any help with this would be a massive help to me.

Note this is not the actual data and rather a sample of the thing I am looking for.

Many Thanks

 

[kweaver]

Please use XL2BB to illustrate your data and expected results so that anyone trying to help you doesn't have to retype the information to test possible solutions.

 

[kweaver]

First off, you can't look-up "New York" if your data sheet only has abbreviations for the city.
You'll need a mapping for cities such as "New York" becomes "NYC" or "San Diego" becomes "SD " (with a space after SD).
With that in mind, B8 on the results sheet might look like this:

=FILTER(Data!A7:F11,LEFT(Data!A7:A11,3)=$B$4)

And, H8 might be:

=IFERROR(TEXTJOIN(",",,FILTER(Data!$G$6:$U$6,"x"=FILTER(Data!G7:U11,B8=Data!A7:A11))),"")

In this example, I've used data for 5 cities.

Maybe this will help and get you to the other section to filter as well.

 

[SNyansa]

First off, you can't look-up "New York" if your data sheet only has abbreviations for the city.
You'll need a mapping for cities such as "New York" becomes "NYC" or "San Diego" becomes "SD " (with a space after SD).
With that in mind, B8 on the results sheet might look like this:

=FILTER(Data!A7:F11,LEFT(Data!A7:A11,3)=$B$4)

And, H8 might be:

=IFERROR(TEXTJOIN(",",,FILTER(Data!$G$6:$U$6,"x"=FILTER(Data!G7:U11,B8=Data!A7:A11))),"")

In this example, I've used data for 5 cities.

Maybe this will help and get you to the other section to filter as well.

Hi kweaver

Ahh sorry my fault, I've cut it out on the second screen grab but the worksheet names are linked to the options in the dropdown. The worksheet itself was named 'New York', not NYC.
I'm away from my desktop at the moment, as soon as I am back within reach I will use XL2BB.

In the meantime I'll have a look at your suggestions.

Appreciate your help so far.

 

[SNyansa]

First off, you can't look-up "New York" if your data sheet only has abbreviations for the city.
You'll need a mapping for cities such as "New York" becomes "NYC" or "San Diego" becomes "SD " (with a space after SD).
With that in mind, B8 on the results sheet might look like this:

=FILTER(Data!A7:F11,LEFT(Data!A7:A11,3)=$B$4)

And, H8 might be:

=IFERROR(TEXTJOIN(",",,FILTER(Data!$G$6:$U$6,"x"=FILTER(Data!G7:U11,B8=Data!A7:A11))),"")

In this example, I've used data for 5 cities.

Maybe this will help and get you to the other section to filter as well.

I am just getting a #CALC error on that first formula. I wonder if it might be simpler to just do multiple IFs (i.e. '=IF($B$4="New York",'New York'!A7,"")) for each named worksheet and copy them across and down? The H8 formula you provided seems to work perfectly though I have only tried it with one city.

 

[kweaver]

=FILTER(Data!A7:F11,LEFT(Data!A7:A11,3)=$B$4)

my first formula (above) filters the data from the DATA sheet that you show in your Capture6.
The formula is on the Results sheet where B4 contains the abbreviated city name using 3-characters (e.g., "NYC" or "SD " [note the space after "SD"]
My data sheet used only rows 7 through 11.

I did NOT have separate sheets for the various cities.