Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Named Range in Excel that goes across worksheets
- Thread starter DanAuber
- Start date
SkipVought
Programmer
Hi,
Don't believe so. I tried setting a range object in VBA as a union of ranges across 2 worksheets and was not able to do so.
So my conclusion is that Naming such a range would not be possible as well.
Skip,
Skip@TheOfficeExperts.com
Don't believe so. I tried setting a range object in VBA as a union of ranges across 2 worksheets and was not able to do so.
So my conclusion is that Naming such a range would not be possible as well.
Skip,
Skip@TheOfficeExperts.com
JPMontreal
Programmer
Hi,
Choose “Multiple consolidation ranges” to do your PivotTable.
jp@solutionsvba.com
Jean-Paul
Montreal
mtljp2@sympatico.ca
Choose “Multiple consolidation ranges” to do your PivotTable.
jp@solutionsvba.com
Jean-Paul
Montreal
mtljp2@sympatico.ca
Hi Skip,
I succeeded in naming a range containing the first three columns from the first three sheets and named it PivotData.
=Sheet1!A:C;Sheet2!A:C;Sheet3!:A:C
The problem is that you can't create a pivot table (or chart) from that data.
You can however create a Pivot table from multiple ranges:
Go to Data->Pivot Table and Pivot Chart Report, select Multiple Consolidation Ranges, and then add the ranges in the next screen.
This will not work with the "combined" Named Range, but you can create individual Named Ranges, and then add them to the list.
I hope this helps!
Peace!![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
Mike
Never say Never!!!
Nothing is impossible!!!
I succeeded in naming a range containing the first three columns from the first three sheets and named it PivotData.
=Sheet1!A:C;Sheet2!A:C;Sheet3!:A:C
The problem is that you can't create a pivot table (or chart) from that data.
You can however create a Pivot table from multiple ranges:
Go to Data->Pivot Table and Pivot Chart Report, select Multiple Consolidation Ranges, and then add the ranges in the next screen.
This will not work with the "combined" Named Range, but you can create individual Named Ranges, and then add them to the list.
I hope this helps!
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
SkipVought
Programmer
I have used consolidate ranges before in Pivot Tables - just never tried to Name such a range until now.
Skip,
Skip@TheOfficeExperts.com
Skip,
Skip@TheOfficeExperts.com
- Thread starter
- #6
Hi JPMontreal,
I did choose “Multiple consolidation ranges” but it failed to pick up the columm headings for use in the pivot wizard....
For clarification - I've got data in 3 columns in 3 worksheets in 1 workbook. The columns all have the same headings. - I want to be able to pivot looking at this data as if it was all in 3 columns combined....
thanks for any help
Dan Auber
I did choose “Multiple consolidation ranges” but it failed to pick up the columm headings for use in the pivot wizard....
For clarification - I've got data in 3 columns in 3 worksheets in 1 workbook. The columns all have the same headings. - I want to be able to pivot looking at this data as if it was all in 3 columns combined....
thanks for any help
Dan Auber
SkipVought
Programmer
Mike,
How did you go about naming that range?
I tried using Insert/Name/Define
=Sheet1!$A:$C,Sheet2!$A:$C
and it did not work.
Skip,
Skip@TheOfficeExperts.com
How did you go about naming that range?
I tried using Insert/Name/Define
=Sheet1!$A:$C,Sheet2!$A:$C
and it did not work.
Skip,
Skip@TheOfficeExperts.com
SkipVought
Programmer
Here is what I experienced...
I did create a range that had a reference
=Sheet1!$A:$C,Sheet2!$A:$C
However, when I select that range in the Name Box, Excel changes the reference to the address of the activecell.
Skip,
Skip@TheOfficeExperts.com
I did create a range that had a reference
=Sheet1!$A:$C,Sheet2!$A:$C
However, when I select that range in the Name Box, Excel changes the reference to the address of the activecell.
Skip,
Skip@TheOfficeExperts.com
- Thread starter
- #9
Actually,
My range did the same thing, I just forgot to mention it. I actually meant that I didn't receive an error after entering the range reference.
Dan,
Have you thought about removing the headers from the second and third sheets, since they are the same as the first?
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
My range did the same thing, I just forgot to mention it. I actually meant that I didn't receive an error after entering the range reference.
Dan,
Have you thought about removing the headers from the second and third sheets, since they are the same as the first?
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
SkipVought
Programmer
Dan,
You can edit the Names or the Row and Column headers in your Pivot Table
Skip,
Skip@TheOfficeExperts.com
You can edit the Names or the Row and Column headers in your Pivot Table
Skip,
Skip@TheOfficeExperts.com
- Thread starter
- #12
Skip,
still not getting there. If I had data like this:
Name Period Score
Dan 1 20
Dan 2 40
Paul 1 50
Paul 2 80
then if i pivoted it - the grey fields I'd be able to move around in the Wizard would be "Name", "Period" and "Score" and they would refer to the data.
If I try and do this where I have two sets of data like above linked using Multiple Consolidation Ranges I get different non-sensical field names ("Row","Column","Value" and "Page 1"
- the pivot is useless.
So to sum up so far:
Multiple Consolidation Ranges doesn't seem to work
Named Ranges across more than worksheet doesn't seem to be possible,
thanks for any further help....getting desperate
dan
Dan Auber
still not getting there. If I had data like this:
Name Period Score
Dan 1 20
Dan 2 40
Paul 1 50
Paul 2 80
then if i pivoted it - the grey fields I'd be able to move around in the Wizard would be "Name", "Period" and "Score" and they would refer to the data.
If I try and do this where I have two sets of data like above linked using Multiple Consolidation Ranges I get different non-sensical field names ("Row","Column","Value" and "Page 1"
- the pivot is useless.So to sum up so far:
Multiple Consolidation Ranges doesn't seem to work
Named Ranges across more than worksheet doesn't seem to be possible,
thanks for any further help....getting desperate
dan
Dan Auber
- Thread starter
- #14
SkipVought
Programmer
![[soapbox]](/data/assets/smilies/soapbox.gif "[soapbox] [soapbox]")
This is one of the big problems I see in virtually every office that I consult in.
Similar data is spread across workbooks and/or worksheets based on some grouping criteria like Time Period (Week, Month, Quarter, Year) or other logical grouping like Geographic Area, Sales Group, Team Leader.
These arbitrary groupings are, in fact, a data element that could be used to keep the data set in a single table, discriminated by that element.
Then such things as Pivot Table reporting, Charting, and other data mining techniques can be used on the set as a whole, in order to glean much valuable data -- often data that could be VERY useful for managing and making informed business decisions!
Skip,
Skip@TheOfficeExperts.com
- Thread starter
- #16
Thanks for that Skip
But there's no way I can move the data onto one worksheet very easily. The data is generated onto the worksheets by another piece of software.
I agree with you generally though...
Having looked at this a bit harder I think it's actually revealed a bug in Excel in that Named ranges don't work across more than one worksheet.
I'd also be very grateful for anyone who could mail me a spreadsheet where they have used Multiple Consolidation Ranges fruitfully.
Dan Auber
But there's no way I can move the data onto one worksheet very easily. The data is generated onto the worksheets by another piece of software.
I agree with you generally though...
Having looked at this a bit harder I think it's actually revealed a bug in Excel in that Named ranges don't work across more than one worksheet.
I'd also be very grateful for anyone who could mail me a spreadsheet where they have used Multiple Consolidation Ranges fruitfully.
Dan Auber
SkipVought
Programmer
I'd like to see one too! Seems that there is ALOT lacking in multiple consolidation ranges.
Skip,
Skip@TheOfficeExperts.com
Skip,
Skip@TheOfficeExperts.com
- Status