GETPIVOTDATA....Excel 2000....Desperation!
 

Hi,

I am posting this as I have exhausted all avenues trying to get a
result; trying Debra Dalgleish's pivot table tips in the process.

I have a data list which amongst other data items, lists:

stores
products
calendar_quarters
currency
sales_value

e.g. Manchester, Sony TVs, 1, GBP

I have two workbooks, one with the list and associated pivot table and
one where I am inserting the data. The pivot table has the following
structu

Page - Stores
Rows - Field Products
Columns - Field Quarters then second field Currency
Data Item - Sales_value ( sum of )

The products have numeric codes all with a general format, and some
have 1/1, 1/2 as well as 2 etc.

It is not strictly necessary for the pivot table to have the "Stores"
page but it helps other users when viewing the information for other
purposes.

The workbook receiving the data has a row for all products, what I
want to do is place the value for total sales for a selected currency
in each quarter, where a value appears. There may not be a record in
the data list for every product in every quarter. My arguments in the
receiving workbook are as follows:

=GETPIVOTDATA('[Product records_TEST2007.xls]Sales_numbers'!$A$3,"2 1
GBP")

The frustrating thing is that a value is returned for some rows in the
pivot table but not others. For example the first row against product
2 for quarter1 is GBP 0, when I request this data I get a #N/A error
yet when I request product 8 quarter 1 GBP 100 the value is returned.

Does anyone have any thoughts on why I am not consistently getting
values returned into the reporting workbook from the source workbook?

Thanks.

--
Posted via a free Usenet account from http://www.teranews.com

GETPIVOTDATA....Excel 2000....Desperation!
 

Hi

If you rearrange your PT, there will be no need to do the calculation
outside of the PT.

Drag Quarters to the row area, followed by Currency, followed by product
Double clicking in the currency field will hide all the products and
just give you a Total.
Double click the filed again to show all the products.
Use the dropdown filter on product to select any individual product.

--
Regards

Roger Govier


"Kev" wrote in message
...
Hi,

I am posting this as I have exhausted all avenues trying to get a
result; trying Debra Dalgleish's pivot table tips in the process.

I have a data list which amongst other data items, lists:

stores
products
calendar_quarters
currency
sales_value

e.g. Manchester, Sony TVs, 1, GBP

I have two workbooks, one with the list and associated pivot table and
one where I am inserting the data. The pivot table has the following
structu

Page - Stores
Rows - Field Products
Columns - Field Quarters then second field Currency
Data Item - Sales_value ( sum of )

The products have numeric codes all with a general format, and some
have 1/1, 1/2 as well as 2 etc.

It is not strictly necessary for the pivot table to have the "Stores"
page but it helps other users when viewing the information for other
purposes.

The workbook receiving the data has a row for all products, what I
want to do is place the value for total sales for a selected currency
in each quarter, where a value appears. There may not be a record in
the data list for every product in every quarter. My arguments in the
receiving workbook are as follows:

=GETPIVOTDATA('[Product records_TEST2007.xls]Sales_numbers'!$A$3,"2 1
GBP")

The frustrating thing is that a value is returned for some rows in the
pivot table but not others. For example the first row against product
2 for quarter1 is GBP 0, when I request this data I get a #N/A error
yet when I request product 8 quarter 1 GBP 100 the value is returned.

Does anyone have any thoughts on why I am not consistently getting
values returned into the reporting workbook from the source workbook?

Thanks.

--
Posted via a free Usenet account from http://www.teranews.com