hi all,

This is cross posted as:
http://www.excelforum.com/excel-gene...total-etc.html

I posted this at the above site yesterday & have had a number of views but
no replies as yet.

In the other post, I have attached a file containing 3 questions about
specific Pivot Table design problems (in Excel 2003). Can you please have a
look at the file & let me know if it is possible to create Pivot tables in
the same way as the "normal" calculation tables that are also included?

The questions are contained within the file & linked to the appropriate area
which will hopefully make it clearer due to the context. I could never make
the problem as specific when just typing into a message but here's a rough
outline...

Q1 is roughly: %age showing row as a proportion of grand total.
(I have seen some posts that suggest this is not possible. Do I need to
include a new column in the PT's source data?)
Q2: sub total of 2 data fields with more columns to the right.
Q3: can the various data/formats be shown in a single PT?
(If not, which I think is likely to be the case, I'll just link them to the
specific PT's on the previous sheet)

The main aim of my use of PT's in this file is to minimise the manual
updating/copying & pasting that is currently done by the current spreadsheet
creator.

Thanks in advance,
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Hi Rob, see answers within.

broro183 wrote:
hi all,

This is cross posted as:
http://www.excelforum.com/excel-gene...total-etc.html

I posted this at the above site yesterday & have had a number of views but
no replies as yet.

In the other post, I have attached a file containing 3 questions about
specific Pivot Table design problems (in Excel 2003). Can you please have a
look at the file & let me know if it is possible to create Pivot tables in
the same way as the "normal" calculation tables that are also included?

The questions are contained within the file & linked to the appropriate area
which will hopefully make it clearer due to the context. I could never make
the problem as specific when just typing into a message but here's a rough
outline...

Q1 is roughly: %age showing row as a proportion of grand total.
(I have seen some posts that suggest this is not possible. Do I need to
include a new column in the PT's source data?)

This can be done, but not while showing the Premium category. Remove
Premium, change the data to show % of column. That's it. Note: it is not
necessary to use the calculated field "Proportion by Hub" here; Sum of
SUM gives the same result since you are looking at % of total and
Proportion is just a scalar of SUM.

Q2: sub total of 2 data fields with more columns to the right.

A PT is not the final solution here, but it can be used as a start. Lay
out a PT based on all the source data (as you did with Q1), place HUB in
rows, Proportion by Hub in columns, and show row totals. Add Premium 1.0
+ Premium 1.1 to get the subtotal. You can use GETPIVOTDATA to
generalize the solution, thus avoiding manual updates. Also fetch each
HUB row total using GETPIVOTDATA, then divide Subtotal by Total to get
the %.

Here's what I mean by using GETPIVOTDATA.

Suppose the PT lives at $H$115. Somewhere, lay out a column of HUB
names. I used P117:P125.

This will get the "Subtotal" on row 117 and fill down:
=GETPIVOTDATA("Proportion By
Hub",$H$115,"HUB.",$P117,"Premium.","1.0")+GETPIVO TDATA("Proportion By
Hub",$H$115,"HUB.",$P117,"Premium.","1.1")

And this will get the Total on row 117 and fill down:
=GETPIVOTDATA("Proportion By Hub",$H$115,"HUB.",$P117)

The idea here is you can place the results table anywhere you like, and
point to the PT by using field names instead of worksheet ranges. That
way, even if the PT expands/shrinks, so long as you have all the HUBs
listed in the results table, you will never have to update it manually.

Q3: can the various data/formats be shown in a single PT?
(If not, which I think is likely to be the case, I'll just link them to the
specific PT's on the previous sheet)

I don't think so either, but these are just the same totals we got in Q2
so there's no need to reinvent the wheel?

Hope this helps!

Thanks Smartin,

I think your suggestions will do everything - I'll check all this out when I
get back into the office tomorrow - it makes sense & I don't know why I
hadn't considered the "getpivotdata" option (one track mind! & maybe
subconsciously beacuse I have been frustrated by the inflexible setup of the
formula when it converts/makes the references into strings - but for this
purpose (design once & use repeatedly) it should be ideal).

I'll be in touch if I have any problems...

Thanks
Rob
__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...



"smartin" wrote:

Hi Rob, see answers within.

broro183 wrote:
hi all,

This is cross posted as:
http://www.excelforum.com/excel-gene...total-etc.html

I posted this at the above site yesterday & have had a number of views but
no replies as yet.

In the other post, I have attached a file containing 3 questions about
specific Pivot Table design problems (in Excel 2003). Can you please have a
look at the file & let me know if it is possible to create Pivot tables in
the same way as the "normal" calculation tables that are also included?

The questions are contained within the file & linked to the appropriate area
which will hopefully make it clearer due to the context. I could never make
the problem as specific when just typing into a message but here's a rough
outline...

Q1 is roughly: %age showing row as a proportion of grand total.
(I have seen some posts that suggest this is not possible. Do I need to
include a new column in the PT's source data?)

This can be done, but not while showing the Premium category. Remove
Premium, change the data to show % of column. That's it. Note: it is not
necessary to use the calculated field "Proportion by Hub" here; Sum of
SUM gives the same result since you are looking at % of total and
Proportion is just a scalar of SUM.

Q2: sub total of 2 data fields with more columns to the right.

A PT is not the final solution here, but it can be used as a start. Lay
out a PT based on all the source data (as you did with Q1), place HUB in
rows, Proportion by Hub in columns, and show row totals. Add Premium 1.0
+ Premium 1.1 to get the subtotal. You can use GETPIVOTDATA to
generalize the solution, thus avoiding manual updates. Also fetch each
HUB row total using GETPIVOTDATA, then divide Subtotal by Total to get
the %.

Here's what I mean by using GETPIVOTDATA.

Suppose the PT lives at $H$115. Somewhere, lay out a column of HUB
names. I used P117:P125.

This will get the "Subtotal" on row 117 and fill down:
=GETPIVOTDATA("Proportion By
Hub",$H$115,"HUB.",$P117,"Premium.","1.0")+GETPIVO TDATA("Proportion By
Hub",$H$115,"HUB.",$P117,"Premium.","1.1")

And this will get the Total on row 117 and fill down:
=GETPIVOTDATA("Proportion By Hub",$H$115,"HUB.",$P117)

The idea here is you can place the results table anywhere you like, and
point to the PT by using field names instead of worksheet ranges. That
way, even if the PT expands/shrinks, so long as you have all the HUBs
listed in the results table, you will never have to update it manually.

Q3: can the various data/formats be shown in a single PT?
(If not, which I think is likely to be the case, I'll just link them to the
specific PT's on the previous sheet)

I don't think so either, but these are just the same totals we got in Q2
so there's no need to reinvent the wheel?

Hope this helps!