Pivot with 2 sheets

[Vashisht106]

Hi,

Looking for a solution where i can prepare pivot with 2 sheets....i tried by doing it with consolidation of sheets but it doesnt help as it doesnt give you much option of making changes in pivot...like if i want Month or region or mprove name in filter field...

Can anyone sugges someting for this.

Thanks

 

[Kamolga]

Hi,

If what you have is data on 2 sheets and you want to merge them in one pivot, there are I believe only 2 solutions:

1. The one you tried: consolidation in one table. The best is often to reduce columns by adding one category column and having a few columns as category items (so much more lines) and have a pivot to summarize the all. If your data is well set up and with that goal, you can do all the required views and filters in Pivot.

2. Power Pivot is allowing to select 2 tables and create links between them. Dates can be linked, regions, salespersons etc. Issue is that Power Pivot is available with very few Excel Versions (business big accounts I believe)

Solution 2B is Access, but that is quite more complicated than Power Pivot as you get to import/export data

 

[Vashisht106]

I am afriad we cant do in power pivot as well, as it only allows to link between 2 tables on one element only...i tried it

 

[Kamolga]

One element? Could you provide the columns of both tables and shortly explain the result you are looking for?

 

[sandy666]

maybe try Alt+D+P ?