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.