Pivot chart - sort by hidden value?

hi

new to excel and new to this forum :-)

i have just started using excel with the goal to visualize some of our firm-data.
i have therefore read a bit about pivot tables, and have been experimenting a bit.

WHAT I TRY TO DO
i try to compare subjects factors to hours used, but setting up two charts.

PROBLEM
but i have got a problem (i guess it's quite simple) that i can't solve.

i have a table that contains
- subject
- hours
- factor

those data i would like to precent in to charts
CHART ONE - FACTOR
- shows the subject
- sorted by factor
- shows the factor

CHART TWO - HOURS
- shows the subject
- sorted by factor
- shows the hours

i can set up the charts, and sort them as needed - but i can't figure out how to hide the factor-bars in the HOURS table?

does anyone know how to do that?

A SECOND PROBLEM
if there are a lot of subjects, and some of them has a factor of '0', then the subjects are not alway sorted the same way in the two charts.
to solve this i guess i need to "sort by FACTOR, then by SUBJECT" but is that possible?


thanks
jorgensen

hi oeldere

i'm very sorry - i wasn't aware of that rule.

iv'e tried your solution, but it's giving me errors - i guess i just need to keep trying.

thank you for the kind help.

jorgensen

i have now been experimenting with VBA and two pivottables - and i can sort them ok, but i wonder if it's possible to sort an collumn by using fields from an other pivot table?

- this way i could sort the pivottable HOURS by using the FACTOR in the FACTOR pivottable - but i guess it's not possible :-/

thanks
jorgensen

hi oeldere

my fault - i did not see the "sheet1" in your file - and got confused :-/

i will look into your solution - and see if i can figure out what you are doing it :-)

- time for bed now.

thanks
jorgensen

Hi oeldere

I'm not sure what your are doing in the sheet you have added, is it possible for you to sort the table "hours", on sheet1 by the factors?

thanks
Jorgensen

hi oeldere

i think i get what you do - you create a new label that contains the RANKED number of factor + the name of the subject, and then sort by the label.

i also need to sort by group - and i think it's getting to complicated for my capabilities / old brain :-(

i really appreciate your input!

jorgensen

hmm i just tried to create a SORT column that contain a combination of FACTOR + SUBJECT, and used that to sort. it seems to work, but i guess it's not bulletproof as the SORT is sortet as text.

You could plot the Factor on a secondary axis, then set its Fill to no fill, and border to no line, then delete the secondary value axis.

Of course.

hi rorya

that was a simple trick - i wasn't aware that i could move the factor to the secondary axis - this makes it possible to align the two diagrams :-)

thanks
jorgensen