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.
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.
