Consolidate Multiple Named Ranges Into One Pivot Table In Excel

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
Hi all, I am having trouble with the consolidation of my named ranges into the one pivot table to finish a dashboard. I have got all the named ranges into the data source. Each named range is the same shape: that is, each consists of 4 columns. Columns are Amount, Animal, Location, and Colour. I only want Amount and Source for the dashboard. I haven't used consolidated ranges before so don't actually know much about them. The resulting pivot table from the consolidated ranges gives a total for each of the intervening columns as well as a total for the Source.


Row LabelsSum of Row
AnimalSheep$ 5.06
ColourSheep -Black$ 1.01
ColourSheep - White$ 1.73
ColourSheep - Mixed$ 2.32
LocationPaddpock 1$ 1.27
LocationPaddock 2$ 3.80
Grand Total$ 15.19

<tbody>
</tbody>

I included the field headers above for clarity.

What i want is :<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (https://www.asap-utilities.com) -->



Row LabelsSum of Row
Sheep -Black$ 1.01
Sheep - White$ 1.73
Sheep - Mixed$ 2.32
Grand Total$ 5.06

<tbody>
</tbody>

Is it possible with multiple consolidated named range pivot tables to exclude columns, or will i have to do this another way?


what are the options? make new named ranges that consist of only the two columns?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top