Hi

I myself prefer dynamic named ranges, which will adjust automatically when
rows are added or deleted.


Arvi Laanemets


"Niek Otten" wrote in message
...
That's why I always include a "terminator" row or column, often with #NA
values to prevent accidental misuse. New rows or columns are inserted
before
this terminator. That way the range defined by the name is automatically
extended.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Tushar Mehta" wrote in message
om...
Yes, Insert | Name Create... is an oft-overlooked XL capability that
can be a productivity boost.

Something to be aware of: With the create names feature, XL only names
the range covered by the table as it currently exists. If one were to
add new columns/rows, one should reestablish *all* the names.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Thanks Bob.

I've learnt a good deal about naming ranges this morning!

I've also dicovered that selecting the whole table and then
Insert/Name/Create... does actually create the ranges in the way that
you
suggest. (I had only put in the range names individually for each
particular
cell.)

Thanks very much for all the help.

Best regards

John


"Bob Phillips" wrote in message
...
John,

The Null is because the ranges do not intersect as Niek suggests. He
is
referring to where the ranges truly intersect, such as if CH =
B1:B10,
and
Train = A2:H2, then =CH Train would give B2. Because you name the
header
cells, that way doesn't work.

Of course, you could extend the ranges, it would work then.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
Thanks to everyone for your help. I shall certainly use the INDEX
function
Arvi, in future, but for now, I guess I'm most interested in the
intersection operator.

Niek - I've tried your method and I seem to get a #NULL! error, but
if
I
do
the same thing, but with Labels rather than named ranges, it seems
to
work
fine.

Any clues on what I'm doing wrong?

Thanks again

John

"Niek Otten" wrote in message
...
Hi John,

The intersection operator is a space character. So =Train CH would
give
you D4.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"John" wrote in message
...
Hi,

Can anyone tell me if you can use "2 dimensional" named ranges?
So,
for
example, if you had cells B1:D1 named UK, FR, CH respectively and
A2:A4
named Car, Boat, Train, is there a way to reference
programatically
(and
or) manually the value of D4 by using "Train,CH" (or some
equivilent).

I understand the use of Offset, but was wondering if there is a
direct
method.

Thanks

John