External References

[Guest]

I was very careful not to embed external paths in a spreadsheet, but then I
noticed that afterwards, the entire path is now hard-coded in my formulae.
Is there an easy way to change formulae that have f:\mysub\mysub2\[myWB.xls]
in all formulae? What I would really like to do is have the formulae not
refer to a path at all.

 

[Guest]

I was very careful not to embed external paths in a spreadsheet, but then I
noticed that afterwards, the entire path is now hard-coded in my formulae.
Is there an easy way to change formulae that have f:\mysub\mysub2\[myWB.xls]
in all formulae? What I would really like to do is have the formulae not
refer to a path at all.

 

[Dave Peterson]

You could use Edit|Replace and replace.
You could use Edit|Links and change the source to the same workbook.

If you're having trouble finding that link,
get Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

I bet you're copying formulas from a cell in different workbook.

If you're doing just a single formula, copy from the formulabar and paste into
the formula bar.

If you're doing lots, I like this:

You could select the range to copy
change all the formulas to strings
edit|replace
what: =
with: $$$$$=
replace all

Copy and paste to the new workbook

and change all the strings back to formulas (in both worksheets!)
edit|replace
what: $$$$$=
with: =
replace all

I was very careful not to embed external paths in a spreadsheet, but then I
noticed that afterwards, the entire path is now hard-coded in my formulae.
Is there an easy way to change formulae that have f:\mysub\mysub2\[myWB.xls]
in all formulae? What I would really like to do is have the formulae not
refer to a path at all.