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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
3,949
Office Version
  1. 365
Platform
  1. Windows
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).
 

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply. I must not have the dynamic functions. FILTER gives me an invalid function error.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
3,949
Office Version
  1. 365
Platform
  1. Windows
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.
 

Flagz

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

ADVERTISEMENT

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.
 

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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!!
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
3,949
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
3,949
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
I'm not having any luck. Must be a typo or something, but thanks again for sharing your knowledge.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,715
Members
412,613
Latest member
EFRATA
Top