K
Kev
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
structure:
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.
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
structure:
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.