Revise multiple formula references at once?

[Kelly]

I have an Excel worksheet where a lot of the cells have
formulas that refer to cells in another workbook. (The
reference in the formula for each cell is slightly
different than the reference in the formulas for the
other cells.)
I made numerous copies of this worksheet in the same
workbook. In multiple cells on each of these new sheets,
I need to change just a few characters of the reference
in the formula. (This change will be the same for each of
the multiple cells on the same sheet.) Is it possible
somehow to select these multiple cells on the same
worksheet and make a global change to the references in
these formulas, leaving the rest of the formula alone?

 

[Robert Christie]

Hi Kelly
Ithink you can hold crtl and click on each sheets tab to
select each sheet in workbook and make a change in one
cell in one sheet to change that cell in each sheet. Not
sure for multiple cells.

HTH

Bob C.

 

[GB]

Hi Kelly
Ithink you can hold crtl and click on each sheets tab to
select each sheet in workbook and make a change in one
cell in one sheet to change that cell in each sheet. Not
sure for multiple cells.

HTH

Bob C.

I think the other tricky bit is to change just the part of the formulae you
want to. You probably need to be more specific so people can help you.
However, if it is just something like changing a sheet name or workbook
name, you may be able to do it with search and replace (press ctrl-H).

Geoff

 

[_Bigred]

what version..

if 2000, you can change the formula in one cell and then left click and drag
on last record (row) to update the formula. (This is assuming calculate
their data on the same sheets & cells)

let me know so I can pin this down better,
_Bigred

 

[Kelly]

Hi Bigred -
I have Excel 2002 (Office XP). I didn't quite follow what
your answer. Please clarify.
Thanks,
Kelly

 

[Kelly]

Hi Bob,
Thanks for your reply.
My problem is a little hard to explain. I don't want to
change the same cell on multiple sheets, I want to change
multiple cells on one sheet. Each of these cells already
has a formula with a reference to another workbook. I
need to change just part of each cell's reference - but
the change is identical for all the cells that need to be
changed. I hope this makes sense.
Thanks,
Kelly

 

[Kelly]

Hi Geoff,
Thanks for your reply. I think you solved my problem
with "Find and Replace"! Thanks! I never thought of that.
But since you asked me to be more specific, here is
another explanation of my problem, which is a little hard
to explain:
I don't want to change the same cell on multiple sheets,
I want to change multiple cells on one sheet (for a bunch
of different sheets). Each of these cells already has a
formula with a reference to another workbook. (Each
cell's reference is slightly different than the other
cells' references.) I need to change just part of each
cell's reference (the sheet name) and the change is
identical for all the cells that need to be changed. I
hope this makes sense.
I tried your suggestion to use "Find and Replace" and so
far I think it is working beautifully. I'm searching for
the part of the reference that needs to be changed (the
sheet name) and then I'm replacing it with the correct
sheet name.
Thanks!
Kelly