Retaining formula cell references when copying a formula to a different cell

Select the cell with the formula. Press F2 to go into Edit mode. Click in the formula bar and select the formula. Copy it. Press Esc to exit Edit mode. Go to the cell where you want the formula and paste it.


Regards, TMS

Check whether this helps you....

1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
2. Select the range to copy.
3. Press Ctrl+C
4. Start Windows Notepad
5. Press Ctrl+V to past the copied data into Notepad
6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
8. Press Ctrl+V to paste.
9. Press Ctrl+` to toggle out of formula view mode.

Source: http://spreadsheetpage.com/index.php...rmulas_take_2/

Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

@ Sixthsense : your method looks useful if you have a lot of formulae that you want/need to copy. Bit tedious for just one cell though

Regards, TMS

We have to say thanks for John Walkenbach for this tip...

Any how I got a huge rep from you and thanks for it

I was just short on some rep to see a last green (which is getting added now) and I was posting continuously but no reps from OP's and I thought it takes another 2 or 3 weeks to see the last green.

But with your kindness I can see it now.... Little bit of joy since first time I got a rep from you....

huge rep from you

No idea what my rep power is these days, but you're welcome.

Little bit of joy since first time I got a rep from you....

Really? I will make more of an effort. TMS

Originally Posted by TMShucks

Really? I will make more of an effort. TMS

Yes it is a joy when we get rep from the Real Guru's