Desperate question about named ranges

[jmartinez2767]

I have created a named range that goes across multiple tabs. The range name is associated with a string that looks like this: ='Customer Retail:Customer Retail (END)'!$A$5:$X$5000. I need to either create a table on a separate sheet that includes all of the data from this range so I can then use various SUMIFS or pivot tables to analyze the data but I cant get anything to work. I am getting an INVALID data source when I use the name of the range or the string above. Please help if you can. Project is late at this point!!! Bit off more than I can chew!! Thanks!!!

 

[BiocideJ]

You cannot use 3D ranges (multi-sheet ranges) as the source of SUMIF formulas or of PivotTables.

You will need to create a roll-up sheet that has all of the data from the individual sheets combined or perform the calculations that you need on each individual sheet and then take the sum of those values.

For instance, in cell Z1 of the first 'Customer Retail' sheet, =SUMIF('Customer Retail'{condition_range1}, {myCondition1}, {sumrange})
then in your roll-up sheet you can just do =SUM('Customer Retail':'Customer Retail (END)'!Z1) to get the result for condition 1.

 

[jmartinez2767]

You cannot use 3D ranges (multi-sheet ranges) as the source of SUMIF formulas or of PivotTables.

You will need to create a roll-up sheet that has all of the data from the individual sheets combined or perform the calculations that you need on each individual sheet and then take the sum of those values.

For instance, in cell Z1 of the first 'Customer Retail' sheet, =SUMIF('Customer Retail'{condition_range1}, {myCondition1}, {sumrange})
then in your roll-up sheet you can just do =SUM('Customer Retail':'Customer Retail (END)'!Z1) to get the result for condition 1.

Thanks so much for your kind reply!!! Is there anyway to quickly move say 40 different tabs all lined up the same onto a SUMMARY page to feed a pivot table? thanks again for your help!!!

 

[BiocideJ]

Actually...
You didn't specify which version of Excel you are using. If it is 2010 or later, you may be able to adapt this solution for multiple ranges.

http://www.contextures.com/xlPivot08.html

If your version is 2007 or earlier you are out of luck in this regard, however.
The only way I really know of to create them into a single range is via copy/paste although someone else may have a more clever idea.