Searching through multiple worksheets

[Klee]

I'm sure the answer is in here somewhere but I've been reading posts that I
just find confusing and I feel like there must be a simple answer that I'm
missing.

I have a workbook with multiple worksheets and I have created a list of the
worksheet names (I named it sheetnames) and put it into a dropdown list in
cell C4 of my summary worksheet. I would like to find a formula that will
list cells b:4 to B100 from whichever worksheet name is chosen in cell C4.

Is this possible?

Thanks very much

 

[Max]

With C4 as the DV cell containing the sheetnames
select a desired result range, eg select D4100 (with D4 active),
then paste this into the formula bar:
=IF(C4="","",INDIRECT("'"&C4&"'!B4:B100"))
then press CTRL+SHIFT+ENTER to array-enter the formula

You should get the returns you seek in D4100
based on the sheetname selected in C4

 

[Klee]

Wow that works great! Thanks so much.
I've never used Indirect or Ctrl+Shift+Enter before so I'm not sure how it
works with If's. I wanted to figure out how to search in the worksheets
without confusing the reader but what I really need the formula to do is
this...

Like I said, the worksheet name is in cell C4 of the summary page (in a drop
down list) and the information that I need to compare is in cells B:4:B:100
of each worksheet.

I have a list on my summary sheet in cells C100;C150.
I would like the formula to return the names in the list (C100:C150) only if
they are not found in the worksheet named in cell C4 in cells B4:B100

Is there a way to add this into the formula that you already provided
=IF(C4="","",INDIRECT("'"&C4&"'!B4:B100")) or have I just entirely confused
you.

Thanks so much for the help!

 

[Max]

Wow that works great! Thanks so much.

No prob, welcome

.. but what I really need the formula to do is this...

Ah so, the *real* question surfaces.
This is a different ballgame

Try this non-array pitch:
In C4 is DV for the sheetnames as before

In D4:
=IF($C$4="","",IF(C100="","",IF(ISNUMBER(MATCH(C100,INDIRECT("'"&$C$4&"'!B4:B100"),0)),"",ROW())))

In E4:
=IF(ROWS($1:1)>COUNT($D$4:$D$54),"",INDEX($C$100:$C$150,SMALL($D$4:$D$54,ROWS($1:1))-3))

Select D4:E4, copy down to E54 to return the required results,
all neatly bunched at the top, viz as per your specs:

.. to return the names in the list (C100:C150) only if they
are not found in the worksheet named in cell C4 in cells B4:B100

---

 

[Max]

Wow that works great! Thanks so much.

No prob, welcome

.. but what I really need the formula to do is this...

Ah so, the *real* question surfaces.
This is a different ballgame

Try this non-array pitch:
In C4 is DV for the sheetnames as before

In D4:
=IF($C$4="","",IF(C100="","",IF(ISNUMBER(MATCH(C100,INDIRECT("'"&$C$4&"'!B4:B100"),0)),"",ROW())))

In E4:
=IF(ROWS($1:1)>COUNT($D$4:$D$54),"",INDEX($C$100:$C$150,SMALL($D$4:$D$54,ROWS($1:1))-3))

Select D4:E4, copy down to E54 to return the required results in col E**,
all neatly bunched at the top, viz as per your specs:

.. to return the names in the list (C100:C150) only if they
are not found in the worksheet named in cell C4 in cells B4:B100

**Minimize/Mask D454 for a neater appearance

---

 

[Max]

The last para should read as:

Select D4:E4, copy down to E54 to return the required results in col E**,
all neatly bunched at the top, viz as per your specs:

.. to return the names in the list (C100:C150) only if they
are not found in the worksheet named in cell C4 in cells B4:B100

**Minimize/Mask D454 for a neater appearance

---