Pivot Table Data Source, point to cell?

[J.W. Aldridge]

Is there any way to point the data source block to a cell.

I want to simply update a cell (i.e. A1) with the range instead having
to open the pivot table and changing from the wizard. Is there a way
to do this?

Example:


Cell A1 contains: '[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536

Data source in pivot table points to cell A1 to get the range.

please tell me there's a way to do this....

thanx.!

 

[Héctor Miguel]

hi, Jeremy !

Is there any way to point the data source block to a cell.
I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table
and changing from the wizard. Is there a way to do this?
Example:
Cell A1 contains: '[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536
Data source in pivot table points to cell A1 to get the range.
please tell me there's a way to do this...

1) add another -initial- apostrophe to A1 cell so it "reads" (in formula bar):
''[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536

2) use something like the following instruction
(either in a sub procedure or from immediate window pane -vba editor-)

activesheet.pivottables(1).sourcedata = application.convertformula([a1],xla1,xlr1c1)

note: convertformula needs some considerations regarding international use/conventions
RC (Row/Column) might be different to other users (also, excel's language/versions)

hth,
hector.