Remember Me?

#1
September 18th 17, 03:42 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2016 Posts: 79
Referencing cells

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

#2
September 18th 17, 09:37 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2015 Posts: 1,058
Referencing cells

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
#3
September 19th 17, 06:51 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2016 Posts: 79
Referencing cells

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

#4
September 19th 17, 07:08 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 18
Referencing cells

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

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

Hi,

Try this,

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

#5
September 19th 17, 07:25 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2016 Posts: 79
Referencing cells

Same result.

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

#6
September 20th 17, 04:32 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2015 Posts: 1,058
Referencing cells

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Patrick C. Simonds Excel Programming 3 October 2nd 09 04:44 PM mirskman Excel Discussion (Misc queries) 1 January 29th 09 09:57 PM PAL Excel Worksheet Functions 0 January 23rd 08 01:20 AM vfoley Excel Discussion (Misc queries) 4 April 30th 07 05:30 PM [email protected] Excel Programming 2 March 23rd 07 01:29 AM

All times are GMT +1. The time now is 05:46 PM.