Finding the Minimum value across multiple sheets.

[mullahraheil]

Hi,

I am working on a data arranged in 3D format, that is, similar data across multiple sheets, and I want to compare data items in a certain cell on all the worksheets that meet certain criteria. For understanding, I have attached a sample workbook for reference.

Nesting IF within MIN condition, I can get the required result if all the data points are on a single worksheet, as in Single Sheet Results worksheet of the attached sample, below is the formula used:
Normal Formula

=MIN(IF($B$4:$B$32=D$1,$C$4:$C$32))

However, if the same data is spread across multiple sheets, the formula fails to generate the result, irrespective if I am using a normal or array formula (example below):
Normal Formula (Result: #REF!):

=MIN(IF(Sheet1:Sheet29!$C$1=D1,Sheet1:Sheet29!$C$2))

Array Formula (Result: #REF!):

{=MIN(IF(Sheet1:Sheet29!$C$1=D1,Sheet1:Sheet29!$C$2))}

How do I fix the formula so that I can compare the data broken across multiple sheets.

[XLent]

You can't do conditional calcs in 3d syntax.

You could try something like...

D2: =MIN(INDEX(N(+INDIRECT("'Sheet"&ROW($1:$29)&"'!C2"))+9.99E+307*(T(+INDIRECT("'Sheet"&ROW($1:$29)&"'!C1"))<>D$1),0))

copied to E2

but, given INDIRECT, it's volatile.

[mullahraheil]

Thanks for the solution. But this definitely complicates things for me a bit. The actual workbook contains approximately 200 sheets and the sheets are not so generously named as Sheet1, Sheet2, etc., but in a proper way to help identify sheets properly. Is there a way here that can allow me to access all the sheets, in somewhat similar way as the formula that you shared above. What I mean is that is there a generic way to access all the worksheets in a workbook, irrespective of the name I assigned (without using VBA)?

[XOR LX]

Hi,

=SUMPRODUCT(0+(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,Sheet1:Sheet29!$C$1)&"</b></a>","//b")=D1),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,Sheet1:Sheet29!$C$2)&"</b></a>","//b"))

Regards

[XLent]

You can't do conditional calcs in 3d syntax

gets schooled by XOR LX... again ;-)

[mullahraheil]

Thank you, XOR LX and XLent. The two of you made my day. Love you for the support and you both deserve Reps. Thanks once again.