Copying and pasting range with formulas which contain $

[lb_33]

Hi
Is there a method to copy and paste a range with formulas which use the $ character.
Example shows that copied range incorrectly refer to cell B$2 whereas it needs to refer to $B11.

OFFSET is probably no good because cells will move around.

Untitled.png

[AliGW]

Not quite what you asked for, but you can do a quick Find & Replace on the copied cells whilst they are still selected.

[JohnTopley]

Using B2 rather than B$2 will give you the result you want if it is simply a copy/paste action as per your illustration.

[lb_33]

Hi, thanks for the suggestions.
Was aware of the Find and Replace method but the issue is that I often forget to do this, since range that is being copied may have been prepared long before, and I don't do it all the time. Just noticed an error by chance as I copied the range multiple times and had to change manually.

Using B2 rather than B$2: I could do this, but the issue is again forgetting after a while. For example, if I copied row 6 (which didn't have the $ character), expecting the value 25, would end up getting 0. As rows within a particular range are also copied.

[AliGW]

One or the other, though - as far as I am aware, Excel is not capable of knowing your intention when you copy and paste, so if you want the $, you'll have to remember to complete the job by F&R.

[lb_33]

OK thanks I thought that may be the case but wondering if there was a way around it.