Excel 2007: Create a pivot table where data is in multible sheets

[Kenneth Andersen]

Hi

I would like to make a pivot table in a file, where the data area is in 15
other sheets in the workbook.

The problem is, that I cannot find the function in Excel 2007, maybe because
I have just got the new version of Excel yesterday, and haven't got used to
the new look.

In Excel 2003 there was a function during the creation of a pivot table,
where it was possible to create the pivottable based on multible areas, but I
don't think it was very usefull, because it wasn't possible to make the same
build up of the pivottable as in a pivottable based on only one data area.

I hope some of you out there can help me solving my problem.

Kind regards
Kenneth Andersen

 

[Roger Govier]

Hi Kenneth

There doesn't seem to be the option for Multiple Consolidation ranges in
XL2007.
However, if whilst in a cell within any of your source table, you type
Alt+D+P it will invoke the old PT wizard and you can select your ranges
from there.

 

[Kenneth Andersen]

Hi Roger

Thank you for your answer. I'm using the danish version of Excel 2007, and
the shortcut Alt+D+P doesn't work. Could you please tell me what the menu
Alt+D+P means. It would help me to find out what the danish menu is.

Kind regards

Kenneth Andersen

"Roger Govier" skrev:

 

[Debra Dalgleish]

It's the shortcut from Excel 2003 to open the Data menu (Alt+D), and
select PivotTable and PivotChart report (P).

 

[Debra Dalgleish]

And instead of a shortcut, you could add the PivotTable Wizard to the
Quick Access Toolbar. In the English version, click the Office button,
then click Excel Options, and click Customize
In the list at the top left choose All Commands
Scroll down to find PivotTable and PivotChart Wizard
Select that command, and click Add, then click OK

 

[Kenneth Andersen]

Hi Debra

Thank you for your answer. The things you wrote, only lead to the ordinary
pivot wizard. To get the oportunity to create a pivot table based on multiple
areas, I think Rogers advise is correct, if it leads to the old pivot wizard,
because in Excel 2003 the wizard let the user choose a function (I do not
know the english name for this function) where it was possible to create the
pivot table based on multiple areas. But my problem is, that the keyboard
shortcut Alt+D+P doesn't work in the danish version of Excel 2007.

Therefore I would like to know what the menu path is, so i can translate it
to danish, and thereby find out what the danish keyboard shortcut is.

Kind regards

Kenneth Andersen

"Debra Dalgleish" skrev:

 

[Peo Sjoblom]

No, it is the old wizard that Debra described how to add to the QAT
The new button is called "Insert PivotTable" but the there is one is called
"PivotTable and PivotChart Wizard"
The latter will give you the option of selecting multiple consolidation
ranges

To find out the keyboard shortcut for a Danish Excel, find someone with
excel 2003 or earlier since Alt + D + P
is a command for the old menu system, Alt will active the menus, D will
select the data menu and P the pivot table that is in the
data menu.


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Kenneth Andersen]

Hi all of you

Peo: Thank you for your answer. It seems that I was wrong about Debra's
answer (sorry Debra). But my problem is, that when I will press the Alt+D+P
Excel tells me something like (translated from danish to english the best I
can) "Office 2003 shortcut: Alt,D. Keep pressing the sequense from the Office
2003 menu, or press Esc to abort"
This happens before i get to pressing P.

The easiest would be to do as Excel 2007 tells me - and keep pressing the
sequense, but nothing happens when I do that.

It is a logical thought to open Excel 2003 and find the shortcut from there
and use it in Excel 2007, but here it is Alt+D+V - and if I try this
shortcut, I get to the problem desricbed above.

I really hope you can help me.

Kind regards

Kenneth Andersen


"Peo Sjoblom" skrev:

 

[Roger Govier]

Hi Kenneth

Just a thought. Although I posted Alt+D+P, the "+" signs do not have to
be pressed.
It is just hold down the Alt key, then whilst this is held down, press D
followed by P, or maybe for your Danish version, DV

Your translation of the message is perfect, and this show up on my
system too, but only as information, not a warning that the operation
cannot be carried out. That's what made me wonder if you were typing a
"+" after D, which would cause the computer to "beep" and not allow you
to continue.

 

[Kenneth Andersen]

Hi everybody

The problem about the menu is solved. But please keep on reading, as i have
further questions.

Roger: I didn't press the +, but as I got the message I just stopped typing,
as I thought I couldn't continue. This was a funny problem, because there
wasn't any problem at all - just a misunderstanding of the message.

Thank you everybody for your help.

But as I wrote above, I have further questions. Now I have made a pivot
table based on multiple data areas, but it seems to be the case, that it
isn't possible to drag and drop rowfields and columnfiels as I would like,
just like in Excel 2003. Do any of you have a solution for that. I cannot use
the consolidated pivottable, unless I can reorganize the data.

Kind regards

Kenneth Andersen


"Roger Govier" skrev:

 

[Roger Govier]

Hi Kenneth

I have to go out right now, so don't have time to give a full answer.
If you have not had a response from others when I get back, I will
respond then.

 

[Debra Dalgleish]

If you create a Pivot Table from multiple consolidation ranges, as you
discovered, you won't get the same pivot table layout that you'd get
from a single range. There's more information here:

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

Perhaps you can store the data on one worksheet, and create the pivot
table from that. You may want to add a column to identify a region or
salesperson, if that's why the data is currently on separate sheets.

 

[Kenneth Andersen]

Hi Debra

I think your'e right. So I have to hope this will be improved in the next
version of Excel.

Thank you all for your assistance.

Kind regards

Kenneth Andersen

"Debra Dalgleish" skrev: