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

Or, is there a difference.


there is a big difference!

Please read this…


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.

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.

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

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…

@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 because I checked before I posted. And in addition it’s not an advice but a statement what I won’t do.

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.

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

But please do it carefully, it’s easy to make mistakes. If the answer solves the question click :heavy_check_mark:.