pivot table data range

  • Thread starter Thread starter dawn.horton
  • Start date Start date
D

dawn.horton

I am creating a pivot table in one excel file and pulling
the data source from another excel file. The problem is
the name of the data source file changes every month to
coordinate with the new reporting month. For an example,
the name goes from this: datasource 403.xls, to this:
datasource 503.xls. How can I enter the range name of this
file without manually changing the date in the range? Can
I use a cell reference in the name range, which I have
tried unsuccessfully?

Help

Dawn
 
1. Can't you just use Edit/Links ?
2. Could try Edit/Replace.
3. I have files with daily name changes. Most work is done by macros.
This uses the feature in Excel that, having both files open at the
same time, if the source file is renamed the target file automatically
changes to match.

I remove old data from the source .xls file. Replace it with new data
(automatic when importing from Access) and then rename the source file
with today's date. Refresh the PTs. Easy peasy.

Oh - you will also like to use a *dynamic* range name so you do not
have to change the source range. Insert/Name/Define ... something
like :-
= OFFSET(SUPER_YP17!$A$1,0,0,COUNTA(SUPER_YP17!$A:$A),30).
Must use *absolute* ranges as shown.


Regards
BrianB
======================================
 
Back
Top