Moving Spreadsheets with formulas attached, between workbooks.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DigiWongaDude - 23 Jan 2008 01:36 GMT

Hi,

I'm encountering broken links after attempting to merge two xls workbooks
together...sigh.

a formula that was =Sales!P34
becomes:
='C:\...\abc.xls'[sales]!P34 (or some such)

locking the formula in the cell directly to the workbook it was created in.

Q: How can I make the reference to the Workbook relative? Is there something
like...

=[thisWorkbook]!Sales!P34

...not an absolute reference?

many thanks !

Paul

Reply to this Message

Otto Moehrbach - 23 Jan 2008 02:56 GMT

Paul
The simplest thing to do is to change the formulas you are moving into
text before you move them and then convert them back to formulas afterwards.
You can do this easily by clicking on Edit - Replace. In the space for
"What to find" put "=" without the quotes. In the space for "Replace with",
put "$$$$$" without the quotes.
Then after you move them, do the same thing in reverse. HTH Otto

> Hi,
>
[quoted text clipped - 19 lines]
>
> Paul

Reply to this Message