Search for value accross multiple sheets, and returm sheetnames

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hello all,

Hopefully someone can help. I have a workbook with multiple sheets. Each worksheet represents a department.
Each department is required to keep a daily log. When the log is full, the log is stored in a numbered box.
On the worksheet we keep track of date of first and last entry, and the box number the book is stored in.
In the Workbook, I have a search worksheet. What I am trying to do is produce a List of departments that
have logbooks stored in a Box number. For example if I type 20 in Cell A4 of the Search worksheet, it will
list all the departments that have a logbook stored in Box 20. I have created a Named Sheets of all departments
call Sheetlist. The formula I am using is as follows:
=INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$C$2:$C$500"),$A$4)>0),0))
$A$4 is where I am typing the Box number
Sheetlist is the named range
C is the column is each worksheet where the box number is located

I placed the formula in cell B4 and hit CTRL,SHIFT and ENTER. It returned the Department, but when I copy the formula down,
it only returns the first sheetname, not all the sheetnames. If I copy it down 5 rows, it returns the first sheetname 5 times,
not the 5 different sheetnames.

Not sure what I am doing wrong.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
The problem is MATCH() - it will always return the first match.

Does your version of 365 have the new dynamic functions? If so, you could use the single cell formula:

=FILTER(SheetList,COUNTIF(INDIRECT("'"&SheetList&"'!$C$2:$C$500"),$A$4)>0)

(There's also a formula solution for older versions of Excel, but it's less elegant).
 
Upvote 0
Thanks for the reply. I must not have the dynamic functions. FILTER gives me an invalid function error.
 
Upvote 0
In that case, try:

=INDEX(SheetList,SMALL(IF(COUNTIF(INDIRECT("'"&SheetList&"'!$C$2:$C$500"),$A$4),ROW(SheetList)-ROW(INDEX(SheetList,1))+1),ROWS(B$1:B1))) array-entered

I have assumed your formula is in B1. The ROWS(B$1:B1) part of the formula generates the numbers 1 , 2, 3 ... i.e. so that you get SMALL ( ___ , 1), SMALL ( ___ , 2), SMALL ( ___ , 3) etc.
 
Upvote 0
Thanks for the reply. the formula is in B:4. I have used box A:4 for the query box number. My plan was to list the boxes down from B:4, so B:5, B6 etc.
Do I keep my original formula in Cell B:4, and then use this formula in B:5, and then copy it down, or should this replace my formula in cell B:4?
Typed the formula in as an array and got a REF! error.
 
Upvote 0
Typed the formula as a non array and got a return. Is there anyway to add a match function, for an exact match? I tried adding before
SMALL function, but keep getting the "too few arguments" error. The reason I ask is the formula is returning
all boxes. Example, if I type 4 in Cell A4, I get all sheets with a 4 in Column C, so box 4 would show, but also box 14, box
24 etc, but I just want box 4. Thanks again for sharing your knowledge!!
 
Upvote 0
Sorry, I'm lost. From your original post, I thought you were looking for a list of sheet names where C2:C500 had a match for cell A4 in the formula sheet. The example below returns Sheet2 and Sheet5 where there is a match. It doesn't return Sheet3 because 14 and 24 aren't a match for 4.

I'm not clear what formula you are using to get a match between 4 and 14, or 4 and 24? Nor I am clear what you want to happen?

AB
1
2
3Find
44Sheet2
5Sheet5
6-
7-
8-
9
10
11SheetList
12Sheet1
13Sheet2
14Sheet3
15Sheet4
16Sheet5
Sheet1
Cell Formulas
RangeFormula
B4:B8B4=IFERROR(INDEX(SheetList,SMALL(IF(COUNTIF(INDIRECT("'"&SheetList&"'!$C$2:$C$500"),$A$4),ROW(SheetList)-ROW(INDEX(SheetList,1))+1),ROWS(B$4:B4))),"-")
Named Ranges
NameRefers ToCells
SheetList=Sheet1!$A$12:$A$16B4:B8

ABC
1
2Blah
3
4Blah
5
6
74
8
9
10Blah
Sheet2

ABC
1
2
3Blah
4
514
6
7
8
924
Sheet3

AB
1
2Blah
3
4Blah
Sheet4

ABC
1
2Blah
3
4
54
Sheet5
 
Upvote 0
It is exactly as you describe, and your example is exactly what I am looking for, but the formula you suggested produced an error, when I entered as an array, so I entered without the array, just "enter". It returned a result, but an incorrect result. It listed a sheet that did not have the number in A4, and also returned sheet names with a variation of the number, ie 24..or 14...when searching for the number 4. Just a thought...could the fact that multiple numbers are present be an issue? For an example using your sheet 1 above, what if the number 4 was in column C more than once? Would that have an effect on the formula. Again, thanks for your help.
 
Upvote 0
You'll need to array-enter the formula, otherwise it may return wrong values.

If you're getting a #REF! error, then perhaps there is an invalid sheet name in SheetList?

You could also try this alternative (non array-entered):

=INDEX(SheetList,AGGREGATE(15,6,(ROW(SheetList)-ROW(INDEX(SheetList,1))+1)/(COUNTIF(INDIRECT("'"&SheetList&"'!$C$2:$C$500"),$A$4)>0),ROWS(B$4:B4)))
 
Upvote 0
I'm not having any luck. Must be a typo or something, but thanks again for sharing your knowledge.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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