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

Hi,
how about this

Dim rngTrain as range, rngCH as Range

Set rngTrain = Range("Train")
Set rngCH = Range("CH")

debug.print Cells(rngTrain.row, rngCH.Column)

That works dunnit?
O

Hi

Define the range B1:D1 as Country, range A2:A4 as Transport, and B2:D4 as
MyTable - then
=INDEX(MyTable,MATCH("Train",Transport,0),MATCH("C H",Country,0))

Or, with MyTable including column A too (A2:D4)
=VLOOKUP("Train",MyTable,MATCH("CH",Country,0),0)

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"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

Thanks O. Yes that's a good solution.

Your not aware of a manual method are you?

J

"OJ" wrote in message
oups.com...
Hi,
how about this

Dim rngTrain as range, rngCH as Range

Set rngTrain = Range("Train")
Set rngCH = Range("CH")

debug.print Cells(rngTrain.row, rngCH.Column)

That works dunnit?
O

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

Not in the way he has defined his ranges.

--

HTH

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


"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

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

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

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

You can also select the range, and type the name directly into the names
box, to the left of the formula bar.

You might want to learn a bit more at
http://www.xldynamic.com/source/xld.Names.html

--

HTH

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


"John" wrote in message
...
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

You're right, Bob!

I use this way of addressing so often that I didn't even test. Shame on me!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Bob Phillips" wrote in message
...
Not in the way he has defined his ranges.

--

HTH

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


"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

I rarely use it, but I thought of it here, so I had to test :-)

Regards

Bob


"Niek Otten" wrote in message
...
You're right, Bob!

I use this way of addressing so often that I didn't even test. Shame on
me!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Bob Phillips" wrote in message
...
Not in the way he has defined his ranges.

--

HTH

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


"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

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

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

John wrote:
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


After activating the cell where you want the result:

on the worksheet =Train CH

programmatically ActiveCell.Formula = "=Train CH"

Alan Beban

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