How to copy an array without changing relative cell references?

[Dmitry]

Hello specialists!
How to copy an array to an array of the same size without changing relative
cell references in the formulas?
Best regards,
Dima

 

[Marcelo]

change the original to absoute, before copy to another range

regards from Brazil
Marcelo

"Dmitry" escreveu:

 

[Gord Dibben]

One method is to do an edit>replace on the original array.

Replace what: =

Replace With: xxx

Replace all

Copy to new range then reverse the edit>replace.


Gord Dibben MS Excel MVP

 

[Ken Johnson]

Hello specialists!
How to copy an array to an array of the same size without changing relative
cell references in the formulas?
Best regards,
Dima

Hi Dmitry,

You could try temporarily removing the equals sign before you copy,
then when you paste the formula it is only text so the references will
not change, then just type in the equals sign.

Ken Johnson

 

[Ken Johnson]

Hello specialists!
How to copy an array to an array of the same size without changing relative
cell references in the formulas?
Best regards,
Dima

Hi Dima,

another way is to copy and paste to any spare cells on the sheet. That
pasted copy has changed its references and is used later.

Then instead of copy/paste use cut/paste to cut and paste the original
formula to the new destination. Cut/paste does not change cell
references, however your cut cell does lose its formula. To overcome
the lose of formula you then copy/paste the copy of the formula in the
spare cells back into the original cells. The cell references will
revert to the original version.

Ken Johnson

 

[Dmitry]

There are too many relative cell references in the array, to change every
reference.

 

[Dmitry]

Thanks Ken Johnson.
How to add the equal sign to all the formulas in the array at once?

 

[Dmitry]

Dependents on the new destination will lose their precedents if I override
the new destination by pasting the array.

 

[Dmitry]

I was wrong that Dependents will lose their precedents.

 

[Dmitry]

I know now.

Thanks Ken Johnson.
How to add the equal sign to all the formulas in the array at once?
"Ken Johnson" <[email protected]> ???????/???????? ? ???????? ?????????:

 

[Ken Johnson]

Hi Dmitry,

Glad you got it sorted!

Ken Johnson