formula problem
 

Hi
I have a spreadsheet which is linked to a source spreadsheet. Links in the
target spreadsheet repeat every 100 rows.
ex; FL_EDEN State Submission Plan 2005-06.xls]Middle'!E1
FL_EDEN State Submission Plan 2005-06.xls]Middle'!E2

formula problem
 

One way you could it:

First, click Edit/Find, enter the = sign in Find What. Replace with %%.
Replace all. This will change your formulas to text. Now copy your formulas
and paste them to another blank worksheet. Go back to your first worksheet
and click Edit/Find, enter %% in Find What. Replace with = and replace all.
This will change your original formulas from text back to formulas.

Now go to the formulas you copied to the empty sheet and cut/paste them back
to the first worksheet starting at row 101. Now click Edit/Find, enter %%VA
in Find What. Replace with =NY and replace all.

Be sure to back up your work. Hope this helps.


"Bob" wrote:

Hi
I have a spreadsheet which is linked to a source spreadsheet. Links in the
target spreadsheet repeat every 100 rows.
ex; FL_EDEN State Submission Plan 2005-06.xls]Middle'!E1
FL_EDEN State Submission Plan 2005-06.xls]Middle'!E2
.
.
.
I would like to copy the above 100 formulas to row101-200 and replace the
VA with another value like NY without changing the rowcount (<---) in the
formula. When I use paste special or insert cells the rowcount at the end of
the formula is changing
VA_EDEN State Submission Plan 2005-06.xls]Middle'!E101 <----
VA_EDEN State Submission Plan 2005-06.xls]Middle'!E101 <-----
.
.
.

result should look like this:
NY_EDEN State Submission Plan 2005-06.xls]Middle'!E1
NY_EDEN State Submission Plan 2005-06.xls]Middle'!E2
.
.
.
How can I do this.



Please Inform.