Ask Your Question

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

asked 2019-10-14 20:30:54 +0200

vomlehn gravatar image

Or, is there a difference.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-10-14 21:02:57 +0200

Craig22 gravatar image


there is a big difference!

Please read this...


edit flag offensive delete link 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.

vomlehn gravatar imagevomlehn ( 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.

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

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

vomlehn gravatar imagevomlehn ( 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..

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

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

answered 2019-10-15 01:24:56 +0200

m.a.riosv gravatar image

updated 2019-10-15 01:25:17 +0200

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.

edit flag offensive delete link 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.

vomlehn gravatar imagevomlehn ( 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 ✔.

m.a.riosv gravatar imagem.a.riosv ( 2019-10-15 21:35:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-14 20:30:54 +0200

Seen: 407 times

Last updated: Oct 15 '19