Can you use a formula to edit another formula in excel?

[Guest]

I have multiple different formulas that I want copied many times. The
formula contains references to particular sheets (tabs at bottom of page).
Everytime I copy the formulas and paste them over a range of cells, I want
the sheet reference to change to a different sheet. How can I do this
without manually changing the sheet reference one by one in these formulas?

Thanks

 

[Guest]

=IF(sheet1!$A1<>"",sheet1!$A1,"")

say your formula is like the one above,
select the entire area with formula
then go to Replace
type sheet1!
with sheet2!
replace all should take a couple of seconds,

 

[Guest]

Pilot,
Excellent solution, I was thinking in VBA code and you cut straight to the
chase.

Engineer:
One thing that I might add...if you have any place on the sheet that has the
same set of characters as the sheet name that you want to replace, and that
is not part of a formula, you can use Edit | Go to | Special to select only
the formulas on your worksheet, then the Find/Replace tool will work only on
the selected cells.

Note: You still could use VBA code to automate the whole process, including
selecting only formulas and the find/replace tool. The advantage would be in
the fact that the worksheet names in the workbook are also available in VBA,
so that if you could define a condition so that you could detect the
worksheet name from code, you would not have to type in the exact names, the
VBA macro would do it for you.
If Pilot's solution has helped or if this needs further clarification,
please let us know.
SongBear

 

[Guest]

I really appreciate your help.
Thanks!!!

Pilot,
Excellent solution, I was thinking in VBA code and you cut straight to the
chase.

Engineer:
One thing that I might add...if you have any place on the sheet that has the
same set of characters as the sheet name that you want to replace, and that
is not part of a formula, you can use Edit | Go to | Special to select only
the formulas on your worksheet, then the Find/Replace tool will work only on
the selected cells.

Note: You still could use VBA code to automate the whole process, including
selecting only formulas and the find/replace tool. The advantage would be in
the fact that the worksheet names in the workbook are also available in VBA,
so that if you could define a condition so that you could detect the
worksheet name from code, you would not have to type in the exact names, the
VBA macro would do it for you.
If Pilot's solution has helped or if this needs further clarification,
please let us know.
SongBear

 

[Guest]

I really appreciate your help.
Thanks!!!

=IF(sheet1!$A1<>"",sheet1!$A1,"")

say your formula is like the one above,
select the entire area with formula
then go to Replace
type sheet1!
with sheet2!
replace all should take a couple of seconds,