Dynamic Named Range and MIN Across Multiple Worksheets?

[JeffK627]

I have an Excel 2010 workbook with a separate worksheet for each month. The sheets are named with the abbreviated month name and the year (Jan 2016, Feb 2016, etc.). I need to create a dynamic named range that includes K1:K34 for only the current month through December (so right now it would only include Sep 2016 through Dec 2016). I then need to get the smallest numeric value in that named range (I assume I can use the MIN function once the range is created). Has anyone done this? Any advice will be appreciated.

 

[Eric W]

I don't believe there's a way to define a range, dynamic or static, that encompasses multiple sheets. There are ways to define 3D functions to do what you want, like this:

=MIN('Sep 2016:Dec 2016'!K1:K34)

However, there's no easy way (maybe no way at all) to change the sheet range within the formula. I tried OFFSET, INDIRECT, INDEX, and a few other ideas. About the best I came up with is something like this:

	A	B	C	D
1	Sheet names			Month
2	Jan 2016	99	99	9
3	Feb 2016	13	13	Minimum
4	Mar 2016	14	14	3
5	Apr 2016	15	15
6	May 2016	16	16
7	Jun 2016	17	17
8	Jul 2016	18	18
9	Aug 2016	19	19
10	Sep 2016	9	9
11	Oct 2016	3	3
12	Nov 2016	7	7
13	Dec 2016	8	8

<colgroup><col style="******* 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =MIN(INDIRECT("'"&A2&"'!K1:K34")) C2 =MIN('Jan 2016'!K1:K34) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D4 {=MIN(IF(ROW(B2:B13)>D2,B2:B13))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I created a list of sheet names in A2:A13. Then I created a MIN function for each sheet. You can use the B2 version or the C2 version. Then put the month in D2, put the D4 formula in, and you should have the result you want. I also had variations where you put an X in column C if you wanted to include that value in the calculation.

You can put all these cells somewhere not visible, or even hide them if you want.

The alternative would be to create a UDF (User Defined Function) that does what you want, like:

=MultiMIN("Sep 2016","Dec 2016","K1:K34")

but that would require VBA. Let me know if this helps.