Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing cells
Using your formula:
the formula in R5 changes to =A6&" "&B6&" "&C6 instead of staying =A5&" "&B5&" "&C5 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing cells
Same result.
Your new formula still changes when cells are inserted (with shift cells down) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a cells value | Excel Programming | |||
referencing cells based on conditions in other cells | Excel Discussion (Misc queries) | |||
Referencing Cells | Excel Worksheet Functions | |||
Referencing other cells | Excel Discussion (Misc queries) | |||
Referencing Cells | Excel Programming |