Keeping named cells relative

[Mr.Ribit]

I am not sure if the "Subject" correctly reflects the question but
here is what I am trying to do.

I have a standard worksheet in Excel 2000. I have named a cell (Q3)O2k
and another cell (I3)Firmware. The insertion of rows and columns in
this spread sheet is inevitable given the nature of what I am doing
with it. When rows/columns are inserted, the named cell stays where it
was created and does not follow it's intended position.

i.e. Insert a column left of I3 and the named cell becomes H3. I want
it to follow I3

I can go into the Macro editor and adjust the reference but very soon
there will be many macros to worry about.

Can I create a named cell and keep it relative to where I create it
regardless of what happens to the woksheet?

Thank You
Thomas

 

[Earl Kiosterud]

Ribit,

Wow. You're saying that the named cell moves LEFT when you insert a column.
I'd be more credulous if you said it moved right. Or if it moved left when
you deleted a column.

You say you want it to "follow I3." I'm not sure what that means. When you
insert a column left of it, the cell, it's contents, and the name, will be
J3. That's normal. If you want the name to continue to refer to I3, define
the name thus:

=INDIRECT("I3")

 

[Mr.Ribit]

Earl

Hmmm, interesting how one person can read something and know exactly
what he is trying to say and then another reads it and well...hence
confusion

So lets try it this way and in the mean time I will try out your
suggestion.

A B C D E :C1 Has the text TOM in it
1 TOM :C1 is also the destination
2 f a Macro "tom"


Insert a column and...

A B C D E F 1 now has the text TOM in it
1 tom TOM :C1 is stll the destination
2 f the MACRO "tom"

I want tom and TOM to always be matched to the same cell regardless of
row or column insertion.

Hope that helps clarify my intentions. Thanks for your input, I'll let
you know in a bit if "=INDIRECT" worked.

Take Care
Mr.Ribit

 

[Earl Kiosterud]

Ribit,

I see now, I think.

Formula references to cells (and names and such) get adjusted when their
cells get moved, such as by an insert. But macro references, like
Range("C1") do not. So it's always best to used named ranges instead for
all macro references, like Range("Tom") instead of straight cell references.
Such references do follow the cell, and so does the macro.

Use the name box, or Insert - Name - Define, to define the names. That
oughtta do it.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

 

[Mr.Ribit]

Earl

Looks like that is the perfect solution. Thank you for your help.

Mr.Ribit