MIN IF function across multiple worksheets

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.

Oeldere, I have a lot of data on each worksheet - each one contains invoices for a year. It's important they stay separate.

My apologies Glenn, see my comments in the other thread.

Originally Posted by oeldere

Why is that important?

To my opinion it is a lot easier to analyse the data if all data are on 1 worksheet.

Excecialy if you want to analyse the data of several years (which is what you want).

Edit:

Thanks Glenn for the link to the earlier question.

I saw i made the same suggestion in #4 of that threat too.

Maybe i am a parrot and i am repeating myself.

Thanks for the multiple responses oeldere. I agree joining the data onto one sheet makes this piece of analysis easier, but I have other formulas that work better with them separate.

Originally Posted by leelnich

Hi all- Use the INDEX function to coerce the INDIRECT function into returning an array of values for the conditional clause. Strangely, it's not necessary for the value clause. I don't know why.

Please Login or Register  to view this content.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Thanks leelnich, that actually works really well. The only catch is it returns 0, I think because some of the cells in D1:D100 are blank. Any idea how I can exclude blank cells?

Originally Posted by oeldere

Can you tell me which formulas that are?

I also want to learn each day.

Nothing special oeldere, it's just that each worksheet contains one financial year. I have summaries for each year that allow me to easily compare one year to the next.