GetPivot Data

  • Thread starter Thread starter navin
  • Start date Start date
N

navin

Hi All,

In a spreadsheet i am using the getpivotdata formula, and the pivot
valuesare stored in separate files.

Eg:

GETPIVOTDATA("Sum of Total$",[wk21sd.xls]Pivot!$A$3,"Name",$A7)

Now my problem is the file name changes every week i.e. wk21sd will
become wk22sd. i am unable to change the week name in the formula. i
tried using offset which gives me the desired week name but the
getpivotdata still does not give any value in result.

Please help.

Thanks,
Navin
 
Why can't you change the week name in the formula?
Hi All,

In a spreadsheet i am using the getpivotdata formula, and the pivot
valuesare stored in separate files.

Eg:

GETPIVOTDATA("Sum of Total$",[wk21sd.xls]Pivot!$A$3,"Name",$A7)

Now my problem is the file name changes every week i.e. wk21sd will
become wk22sd. i am unable to change the week name in the formula. i
tried using offset which gives me the desired week name but the
getpivotdata still does not give any value in result.
Click to expand...
 
I can't see how you have used an Offset here (maybe I am too tired and not
seeing the obvious), but I can see how you could use INDIRECT to reference
the Pivot Table starting cell, or even a named range which you could simply
update in the Names dialogue.

Using an Indirect will only work if the target workbook is open, although it
could be constructed to calculate the correct filename based on another value
such as a cell or even TODAY().

Do you have page fields for this pivot table? Is A3 actually between the
page field and the main table body (which fails, IME). Can you try changing
A$3$ to maybe B$5$ (any cell within the pivot table will do).

Are you getting no results at all, or some kind of error value?
 
Back
Top