Complex multiple worksheet formula - Is this possible?

SNyansa

New Member
Joined
May 3, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture5.PNG
    Capture5.PNG
    27 KB · Views: 6
  • Capture6.PNG
    Capture6.PNG
    35.4 KB · Views: 5

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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:

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

And, H8 might be:

Code:
=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.
 
Upvote 0
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:

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

And, H8 might be:

Code:
=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.
 
Upvote 0
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:

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

And, H8 might be:

Code:
=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.
 
Upvote 0
Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top