Consolidate Named Ranges

[Tesnobay]

Hi,
Can named ranges be consolidated into a new shorter name? I have data for each month in 2018; JanData, Febdata, etc...thru DecData. I use this data to compute a monthly/daily rolling average. The formula has become quite lengthy. I would like to continue this into 2019, however the formula will grow exponentially. Can the data be consolidated into one named range instead of 12? (i actually started in April, so it's 9 ranges)

=SUM(Aprildata,Maydata,JuneData,JulyData,AugustData,SeptemberData,OctoberData,NovemberData,DecData)/SUM(COUNT(Aprildata),COUNT(Maydata),COUNT(JuneData),COUNT(JulyData),COUNT(AugustData),COUNT(SeptemberData),COUNT(OctoberData),COUNT(NovemberData),COUNTIF(DecData,"<>0"))

 

[sandy666]

You can try Append all defined ranges via PowerQuery (Get&Transform), load result to the sheet then use formula on the result table.

 

[Eric W]

Maybe something like this:

	A	B	C	D	E	F	G	H	I
AprilData
2	MayData
JuneData
JulyData

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

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

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

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

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

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

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

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

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

</tbody>

SummarySheet


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]=SUMPRODUCT(SUBTOTAL(9,INDIRECT(MonthData)))/(SUMPRODUCT(SUBTOTAL(2,INDIRECT(<fon
MonthData</fon
)))-H1)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=SUMPRODUCT(--(INDIRECT(LOOKUP("zzzz",MonthData))=0),--ISNUMBER(INDIRECT(LOOKUP("zzzz",MonthData))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]MonthData[/TH]
[TD="align: left"]=OFFSET(SummarySheet!$F$1,0,0,COUNTA(SummarySheet!$F:$F))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


In this example, column A is AprilData, B is MayData, etc. Then I created another range, which has the names of the other ranges in it. I called this MonthData. You can see the formula I used to create it in the Name Manager. Whenever you add another name to the end of the list in column F, the range will dynamically increase.

The H1 formula is the equivalent of your COUNTIF for the final month. It looks up the last range in the MonthData list, then finds all the actual zeros in that range.

Then the G1 formula is the equivalent of your formula. So in other words, just add your new range to the end of the list in F, and your formula will automatically adapt to include it.

Hope this helps!