Changing external links

  • Thread starter Thread starter john w
  • Start date Start date
J

john w

Each week I create a new data file with updated information. I then
enter data from that file into a summary spreadsheet which contains one
row for each week, by copying the prior weeks row. Each cell in the
row has an external link to data in the weekly file:

='C:\Documents and Settings\John \My Documents\Finance\Bank\[Summary
12-1-06.xls]Data'!$J$1

I have to manually edit each cell to change the date so that the new
weekly file is referenced -next week I would change 12-1-06 to
12-8-06, leaving everything else unchanged. The data cell referenced
is different in each column but doesn't change from week to week,
only the name of the file does.

I also don't want any changes made to the rows above which link to
different weeks.

What I would like to do is input the new date into one cell and have
each formula refer to that cell rather than to the explicit date but I
can't figure out how to do it or whether it is even possible.

Any help would be greatly appreciated.
 
john said:
Each week I create a new data file with updated information. I then
enter data from that file into a summary spreadsheet which contains one
row for each week, by copying the prior weeks row. Each cell in the
row has an external link to data in the weekly file:

='C:\Documents and Settings\John \My Documents\Finance\Bank\[Summary
12-1-06.xls]Data'!$J$1

I have to manually edit each cell to change the date so that the new
weekly file is referenced -next week I would change 12-1-06 to
12-8-06, leaving everything else unchanged. The data cell referenced
is different in each column but doesn't change from week to week,
only the name of the file does.

I also don't want any changes made to the rows above which link to
different weeks.

What I would like to do is input the new date into one cell and have
each formula refer to that cell rather than to the explicit date but I
can't figure out how to do it or whether it is even possible.

Any help would be greatly appreciated.
Click to expand...
 
The message above refers to using Excel.
Should have stated that originally and messed up the 2nd message
trying to explain.

john said:
john said:
Each week I create a new data file with updated information. I then
enter data from that file into a summary spreadsheet which contains one
row for each week, by copying the prior weeks row. Each cell in the
row has an external link to data in the weekly file:

='C:\Documents and Settings\John \My Documents\Finance\Bank\[Summary
12-1-06.xls]Data'!$J$1

I have to manually edit each cell to change the date so that the new
weekly file is referenced -next week I would change 12-1-06 to
12-8-06, leaving everything else unchanged. The data cell referenced
is different in each column but doesn't change from week to week,
only the name of the file does.

I also don't want any changes made to the rows above which link to
different weeks.

What I would like to do is input the new date into one cell and have
each formula refer to that cell rather than to the explicit date but I
can't figure out how to do it or whether it is even possible.

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Back
Top