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
Sheet names	Month
Jan 2016
Feb 2016	Minimum
Mar 2016
Apr 2016
May 2016
Jun 2016
Jul 2016
Aug 2016
Sep 2016
Oct 2016
Nov 2016
Dec 2016

<colgroup><col style="******* 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="******* 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="******* 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="******* 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="******* 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=MIN(INDIRECT("'"&A2&"'!K1:K34"))[/TD]
[/TR]
[TR]
[TH="******* 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=MIN('Jan 2016'!K1:K34)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="******* 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="******* 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="******* 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="******* 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]{=MIN(IF(ROW(B2:B13)>D2,B2:B13))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





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.