Hi forum,

I currently use a formula that counts the number of times an item appears across multiple worksheets. It looks like this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A10&"'!B1:B100"),C1))

Where:

A1:A10 contains the list of worksheet names to search (basically one per year)
B1:B100 is the column in which the item appears on each worksheet
C1 is the item name to be searched

The bit I'm interested in is the INDIRECT("'"&A1:A10&"'!B1:B100"). It allows me to keep adding worksheets each year and never have to retype my basic formula. I'd like to use this formula to search the same column across all worksheets, but return the first corresponding value of an adjacent column it finds. This is what I'm trying to use:

{=(MIN(IF(INDIRECT("'"&A1:A10&"'!B1:B100")=C1,INDIRECT("'"&A1:A10&"'!D1:D100"),"-")))}

Where:

A1:A10 contains the list of worksheet names to search
B1:B100 is the column in which the item appears on each worksheet
C1 is the item name to be searched
D1:D100 is the column from which the first item is to be returned

But it returns #VALUE!

If I replace the INDIRECT formula with the name of the worksheet, it works fine i.e.

{=(MIN(IF('2017'!B1:B100=C1,INDIRECT('2017'!D1:D100"),"-")))}

but I want to get the formula to look at all the worksheets.

Any help?

Thanks.