Copying cells or worksheet - Help!

  • Thread starter Thread starter Dan E
  • Start date Start date
D

Dan E

Newbie-ish question - I want to copy either a worksheet or the cells in the
worksheet from version A of a workbook to version B of that workbook. The
contents of the cells are formulae that refer to other sheets in the
workbook. When I do the copy, the result is that Excel 2000 outsmarts me
and thinks I want to continue to refer to the original cells (in version A),
so it inserts a reference to version A of the workbook. All I want to do is
copy the formulae without any change to the formulae - is there a way to do
that? The answer is probably very simple, but I can't find it - it's
driving me nuts!

TIA,

Dan E
webbie(removethis)@preferredcountry.com
 
I change all my formulas to text, do the copy|Paste and then change them back to
formulas.

I do:
Edit|replace
change =
to $$$$$$ (some unique string)

do the copy|paste
then edit|replace
change $$$$$$
to =

In both workbooks.
 
Dave - sorry to be thick - don't quite follow. Although the formulae are
essentially similar in small blocks, there are many blocks, and many
differnt flavors of formulae, all of which refer to other worksheets.
They're of this level of complexity -
=IF(OR(C6="",C6="-"),"-",IF(RIGHT(C6,2)="OR","-",LOOKUP(C6,Crib!$A$1:$A$300,
Crib!$B$1:$B$300))) - and I'm working with a corporate mandated layout. Not
sure I understand how to do what you're suggesting... Any additional clues
gratefully received!

TIA

Dan E
 
I'm assuming that both the sending and receiving workbooks have worksheets named
Crib. (right?).


When you do the first edit|replace, your formula:
=IF(OR(C6="",C6="-"),"-",IF(RIGHT(C6,2)="OR","-",
LOOKUP(C6,Crib!$A$1:$A$300,Crib!$B$1:$B$300)))

Will be converted to just plain old text:

$$$$$$IF(OR(C6$$$$$$"",C6$$$$$$"-"),"-",IF(RIGHT(C6,2)$$$$$$"OR","-",
LOOKUP(C6,Crib!$A$1:$A$300,Crib!$B$1:$B$300)))

Copy and paste as much as you want.

Then when you do the second replacement ($$$$$$ to =), it'll get converted back
to a formula. The Crib will now refer to the Crib worksheet in the same
workbook.

===
If you don't like that option, copy|paste as regular formulas. But then use
Edit|links to change your links to this workbook.
 
Dave - I wrote the below seconds after I sent my original reply, and for
whatever reason Outlook Express did not send it - many thanks for your kind
follow up explanation - I really appreciate it.
____________________

OH! I think I just realized what you mean - just replace each instance of
"=", right? Then undo that after pasting! Brilliant! Duh - I must be
having a bad hair day.

Thanks a bunch!

Dan E
 
Dave - seconds after I wrote my reply above, I realized you just meant
replace "=" with "unique", then reverse after copying. Brilliant - had not
occurred to me. Then because I have not taken the time to understand
Outlook Express, it did not send (and then lost) my 'seconds later'
response... Apologies, and thanks a million. You might enjoy "The Code
Book" by Simon Singh - similar thinking - symmetric and asymmetric functions
giving rise to public key encryption, plus from an earlier time the
entrapment of Mary Queen of Scots... - great read about cryptography through
the millennia.

Thanks again,

Dan E
 
Glad you got it working.

I'm sure that the technique didn't originate with me. But I find it useful,
too.

Dan said:
Dave - seconds after I wrote my reply above, I realized you just meant
replace "=" with "unique", then reverse after copying. Brilliant - had not
occurred to me. Then because I have not taken the time to understand
Outlook Express, it did not send (and then lost) my 'seconds later'
response... Apologies, and thanks a million. You might enjoy "The Code
Book" by Simon Singh - similar thinking - symmetric and asymmetric functions
giving rise to public key encryption, plus from an earlier time the
entrapment of Mary Queen of Scots... - great read about cryptography through
the millennia.

Thanks again,

Dan E
Click to expand...
 
Back
Top