Cell R5 contains =A5&" "&B5&" "&C5

When I select cells A5, B5 and C5 then click 'Insert' and "Shift cells down", the formula in R5 changes to =A6&" "&B6&" "&C6

How can I write the formula in cell R5 so it is always =A5&" "&B5&" "&C5

Cell R5 contains =A5&" "&B5&" "&C5

When I select cells A5, B5 and C5 then click 'Insert' and "Shift cells down",
the formula in R5 changes to =A6&" "&B6&" "&C6

How can I write the formula in cell R5 so it is always =A5&" "&B5&" "&C5

Try...

=$A5&" "&$B5&" "&$C5

...so the formula becomes column-absolute/row-relative

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Using your formula:

the formula in R5 changes to =A6&" "&B6&" "&C6
instead of staying =A5&" "&B5&" "&C5

On Tuesday, 19 September 2017 23:21:46 UTC+5:30, GARYWC wrote:
Using your formula:

the formula in R5 changes to =A6&" "&B6&" "&C6
instead of staying =A5&" "&B5&" "&C5


Hi,

Try this,

=$A$5&" "&$B$5&" "&$C$5

Same result.

Your new formula still changes when cells are inserted (with shift cells down)

Same result.

Your new formula still changes when cells are inserted (with shift cells
down)

Ok, then, Excel is holding the ref in the shift! Try it by doing the insert in
row 6 and copy the row 5 values down, then insert new values in row 5.

Alternatively, used sheet-level defined names for refs and see what happens.
Make the defined names column-absolute/row-relative as in my initial reply. Now
when you insert at row5 the formula in R5 will return 0!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion