F
Forrest
I need to group the data in a pivot table, why will it not allow me to?
D
Debra Dalgleish
If the field in the source data contains blank cells, or cells with
text, you won't be able to group the data. There are some suggestions
here for fixing the problem:
http://www.contextures.com/xlPivot07.html#Problems
text, you won't be able to group the data. There are some suggestions
here for fixing the problem:
http://www.contextures.com/xlPivot07.html#Problems
F
Forrest
The tip did not help however, since you wrote the book, literally, let me
explain what I need to do. I need to produce a series of charts that pivot
between 5,7,9 and 11 columns of data, so the number of x axis values will
change and the chart has to expand or shrink to fit the data. It cannot show
blank values. I can accomplish this by turning off or on the data values,
but they need to be pivoted by group not by indivudual column. Please help.
explain what I need to do. I need to produce a series of charts that pivot
between 5,7,9 and 11 columns of data, so the number of x axis values will
change and the chart has to expand or shrink to fit the data. It cannot show
blank values. I can accomplish this by turning off or on the data values,
but they need to be pivoted by group not by indivudual column. Please help.
D
Debra Dalgleish
Can you give an example of what you're trying to do, and the data you
want to group?
want to group?
F
Forrest
Ok, I have produced a spreadsheet that calculates cuts for wood strips called
"camber" for beam deflection during bridge construction. Camber strips are
placed along the length of a beam and start at 5/8" at each end and can go as
high as 6" in the center in a parbolic arc. Beam spans may be up to 90 feet
long, but the individual pieces of wood can only be 10' long maximum and are
cut in a straight line, so the intermediate cut heights are interpolated.
The camber will be made of either 4 individual wood segments, 6 segments, 8
segments or 10 segments, depending on the span length.
column headings read as such (seperated by colon
Group:HLA:cut1:cut2:cut3:cut4:cut5:cut6:cut7:cut8:cut9:cut10:cut11
Data in the columns might read
1:avg:5/8:3/4:1:3/4:5/8:0:0:0:0:0:0
or possibly
1:avg:5/8:7/8:1:1-1/2:2:1-1/2:1:7/8:5/8:0:0
Don't worry about the group and HLA headings. However, the chart must show
cuts 1-5 or cuts 1-7 or cuts 1-9 or cuts 1-11 and 4 charts cannot be shown
simultaneously. The data cells will be filled based on the length of the
span. the x axis will be plotted as the length of the pieces. I was able to
accomplish this previously by overlaying 4 normal charts on top of each other
but that has some problems. If I can use the pivot chart to switch between
these groups of data it would be much cleaner.
"camber" for beam deflection during bridge construction. Camber strips are
placed along the length of a beam and start at 5/8" at each end and can go as
high as 6" in the center in a parbolic arc. Beam spans may be up to 90 feet
long, but the individual pieces of wood can only be 10' long maximum and are
cut in a straight line, so the intermediate cut heights are interpolated.
The camber will be made of either 4 individual wood segments, 6 segments, 8
segments or 10 segments, depending on the span length.
column headings read as such (seperated by colon
Group:HLA:cut1:cut2:cut3:cut4:cut5:cut6:cut7:cut8:cut9:cut10:cut11
Data in the columns might read
1:avg:5/8:3/4:1:3/4:5/8:0:0:0:0:0:0
or possibly
1:avg:5/8:7/8:1:1-1/2:2:1-1/2:1:7/8:5/8:0:0
Don't worry about the group and HLA headings. However, the chart must show
cuts 1-5 or cuts 1-7 or cuts 1-9 or cuts 1-11 and 4 charts cannot be shown
simultaneously. The data cells will be filled based on the length of the
span. the x axis will be plotted as the length of the pieces. I was able to
accomplish this previously by overlaying 4 normal charts on top of each other
but that has some problems. If I can use the pivot chart to switch between
these groups of data it would be much cleaner.
D
Debra Dalgleish
Do you need a pivot table for this? Perhaps a dynamic chart would be
better. Jon Peltier has some examples:
http://www.peltiertech.com/Excel/Charts/Dynamics.html
better. Jon Peltier has some examples:
http://www.peltiertech.com/Excel/Charts/Dynamics.html
F
Forrest
I wasn't aware this option existed. It sounds like a good solution possibly.
Thanks.
Thanks.