Excel pivot table query

Spacedman · 7 Jan 2009 at 13:40

Afternoon all.

I have been tasked with collating all our data into one place, quite a big job really and ive made good progress however i have one small problem;

I want to sort by element which is fine however when i want to sort deeper by type of element the type drop down menu has the types of all elements. I want it to only display the type of the element that has been selected. Is there anyway to do this?

Many thanks!

edit: if no one knows what im talking about please say and ill try to phrase it better, ta

Spacedman · 8 Jan 2009 at 09:11

Bump for pivot table justice!

What i want to do is filter the types of element by the element selected in a different drop down menu.

Cobertizo · 8 Jan 2009 at 19:32

Spacedman said:
edit: if no one knows what im talking about please say and ill try to phrase it better, ta

Yes please!

Though first of all, is there a list of "element"s (don't fully understand what that is) for which you wish to view data one at a time? If so, bung it in the Page section of the pivot table. You can then filter in a drop down there.

So... Say you have data like this:

Code:

Company      Product        Value
ABC          Orange         3456
CDE          Lemon          54
ABC          Lemon          56435
ABC          Melon          75
CDE          Orange         8345
CDE          Orange         865
EFG          Melon          2345
EFG          Lemon          7541
CDE          Melon          243
ABC          Melon          775

(Don't ask!

)

Then if you put Company in the Page bit, Product in the left column, and Value in the data field, then you could show, for example, only the EFG related stuff by filtering by Company. Can't show more than one Company at once by doing that, though.

I suspect that my explanation may be as bad as your description of the issue.

But hopefully you get what I'm on about!

Spacedman · 8 Jan 2009 at 21:27

Firstly thanks for replying, its a confusing topic.

Basically :

The element is part of a building i.e. external walls and the type is for the different types of external wall i.e. masonry or clad.
Now what i want to happen is if i stick the element menu and the type in the page section, when i select external walls it will automatically filter the items stored in the type column (which i also want to put in the page tab) the only options you can see/select fit the element selected. As it stands it shows everything under the frame from steel frame to sanitary ware.

What you end up with is two drop down menus in the page section with one telling the other what to display in its drop down menu!

Sorry, confusing myself just thinking about it