# When defining names, what is the difference between $c$26 and c26

Or, is there a difference.

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

there is a big difference!

Craig

more

My question was specifically related to the behavior with defining names. For example, it appears that, when you insert a row, the name's definition does not change, so that name no longer refers to the cell it used to refer to.

( 2019-10-14 22:22:23 +0200 )edit

You are right - but I won't never ever use relative addressing when it comes to define a named range - just make a copy of a formula containing a named range using relative addressing and watch CTRL+F3 (Sheet -> Named Ranges and Expressions -> Manage .... (The references are broken and showing #REF!, when in any cell not using the range, and the range has different meaning in different cells having a formula containing the range). Just don't do that, if you want to understand what's going on in your sheet at some later time.

( 2019-10-14 22:22:46 +0200 )edit

Yes, this behavior was pretty surprising and why I asked the question. Thanks

( 2019-10-15 02:40:15 +0200 )edit

I won't never ever use relative addressing

That of course depends on what you want to achieve. If you want to address cells always relative to the position where you use the name in a formula expression it's of course the very right thing to do. Also, using a named expression that when deleting a referenced cell (range) does not produce a #REF! error but instead just shifts things according to the new position may be exactly a feature to use. Yes, one needs to fully understand how things work..

( 2019-10-15 12:32:35 +0200 )edit

@erAck - please note what I wrote "showing #REF!, when in any cell not using the range (move to any cell not in the named range and not containing a formula using the named range) and this is true at least for my LO 6.3.2.2 because I checked before I posted. And in addition it's not an advice but a statement what I won't do.

( 2019-10-15 13:34:43 +0200 )edit

Using relative references in a Named Expression, makes crucial, where you are when the expression it's defined because The relative references changes in relation with the cell when they are copied.

Test this:
Go to A1, define a named expression RelativeA1F1 Sheet1.F1
Go to B1, define a named expression RelativeB1F1 Sheet1.F1
So both have the same relative formula.
Go to E1, introduce 'Ref E1'
Go to F1, introduce 'Ref F1'
Go to G1, introduce 'Ref G1'

Goto to A1, introduce =RelativeA1F1, result = Ref F1
Goto to A1, introduce =RelativeB1F1, result = Ref E1
Both named expressions have the same relative formula, but as can be seen, they are relative to the cell active when they are created.

They have another advantaje, deleting a column/row referenced by a named expression doesn't broke them, while direct references in a formula gets an #REF! error.

more

Thanks! I was having issues with deleting rows and was hoping relative references could help. Absolute references were giving me no end of difficulties.

( 2019-10-15 02:43:15 +0200 )edit

But please do it carefully, it's easy to make mistakes. If the answer solves the question click ✔.

( 2019-10-15 21:35:58 +0200 )edit