Copy/Paste Formulas from one Excel document to another

[amirah_almu7aka]

hey, im new here. i got a big issue when i want to copy a formula from one excel document to another one(both are opened within the same instance). the problem now is that when i paste the formula into the new document it does so only with cell references to the old document. is there any way that i can simply paste the formula "as is", meaning the cells shouldnt be referenced back to the document where it was copied from but to the new document where it is pasted.

thanks very much advance!
michael

[Domski]

For one formula just select it, press F2 and then highlight the formula and copy it and then paste it where you want.

For a range of formula:

- Do a Find Replace on the formula changing = to ##
- Copy the range and paste it wherever you want it to go
- Do another Find Replace changing the ## back to =

Dom

[godlev]

yes you can copy the formula from the formula edit row above.

see attachment screenshot:example-formula-copy.jpg

[amirah_almu7aka]

thanks very much for the hints! the find and replace feature did it for me! it's still not ideal but it saves a lot of work!

i was applying that to a range of formulas btw.

otherwhise is there no standard feature in excel 2010 that would save you from find-replacing the references to the file where the formulas were copied from?

a vlookup from a pasted formula wud like this for example: "VLOOKUP(H47,'[FILENAME.xlsm]WORKSHEET'!$AL$16:$AR$501,6,FALSE)" what i need is "VLOOKUP(H47,WORKSHEET!$AL$16:$AR$501,6,FALSE)"...

thanks
michael

[Domski]

Nope, formula will pretty much always adjust relative to where you are pasting them unless you convert them into a fixed text string first.

Dom