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

Using your formula:

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

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

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

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

Same result.

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


  #6   Report Post  
Old 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
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


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

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017