INDIRECT with dynamic range
 

Is it true that you cannot use the INDIRECT function to call a dynamic range
name from another cell? I have a dynamic range named Cars. Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can index any of
a set of tables simply by storing the table name in a reference cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call a dynamic range
name from another cell? I have a dynamic range named Cars. Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

Yes, I think that this will work well - except that it does not seem to work
at all if the range is dynamic. I have the range defined as a formula using
the InsertNameDefine menu (it does not appear on the drop down list in the
menu bar). Do you find this to be the case also?

"Gary''s Student" wrote:

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can index any of
a set of tables simply by storing the table name in a reference cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call a dynamic range
name from another cell? I have a dynamic range named Cars. Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

Give me a more specific example.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Yes, I think that this will work well - except that it does not seem to work
at all if the range is dynamic. I have the range defined as a formula using
the InsertNameDefine menu (it does not appear on the drop down list in the
menu bar). Do you find this to be the case also?

"Gary''s Student" wrote:

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can index any of
a set of tables simply by storing the table name in a reference cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call a dynamic range
name from another cell? I have a dynamic range named Cars. Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

Instead of defining Cars by simply selecting the range and typing "Cars" into
the title bar, go to InsertNameDefine and in the Name box type in "Cars2"
then in the 'refers to' bar type in "=offset(B2,0,0,3,3)" then select Add.

Now replace the text in A2 with "Cars2". I find it doesn't work despite the
fact that it is still a legally defined range.

"Gary''s Student" wrote:

Give me a more specific example.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Yes, I think that this will work well - except that it does not seem to work
at all if the range is dynamic. I have the range defined as a formula using
the InsertNameDefine menu (it does not appear on the drop down list in the
menu bar). Do you find this to be the case also?

"Gary''s Student" wrote:

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can index any of
a set of tables simply by storing the table name in a reference cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call a dynamic range
name from another cell? I have a dynamic range named Cars. Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

Now we are getting somewhere.

Take you equation
=offset(B2,0,0,3,3)

and insert it in a cell and it displays:
#VALUE!

What is more important is that if you click in the cell with the formula,
you will see it refers to a single cell: B2 rather than a small table.
Maybe this annoys INDEX()??
--
Gary''s Student - gsnu200746


"Arun" wrote:

Instead of defining Cars by simply selecting the range and typing "Cars" into
the title bar, go to InsertNameDefine and in the Name box type in "Cars2"
then in the 'refers to' bar type in "=offset(B2,0,0,3,3)" then select Add.

Now replace the text in A2 with "Cars2". I find it doesn't work despite the
fact that it is still a legally defined range.

"Gary''s Student" wrote:

Give me a more specific example.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Yes, I think that this will work well - except that it does not seem to work
at all if the range is dynamic. I have the range defined as a formula using
the InsertNameDefine menu (it does not appear on the drop down list in the
menu bar). Do you find this to be the case also?

"Gary''s Student" wrote:

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can index any of
a set of tables simply by storing the table name in a reference cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call a dynamic range
name from another cell? I have a dynamic range named Cars. Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

That's because it's a range and not a single value. If you entered the
formula '=cars' in the cell you would also get the #VALUE! error - yet this
range name works in the INDIRECT function as you showed earlier.

The reason that B2 highlights is that it is the cell value specified in your
formula. This issue is really with the INDIRECT function and not with the
INDEX function. If you remove the INDIRECT part, the INDEX function works
perfectly well regardless of how you define the range (using cars or cars2).

"Gary''s Student" wrote:

Now we are getting somewhere.

Take you equation
=offset(B2,0,0,3,3)

and insert it in a cell and it displays:
#VALUE!

What is more important is that if you click in the cell with the formula,
you will see it refers to a single cell: B2 rather than a small table.
Maybe this annoys INDEX()??
--
Gary''s Student - gsnu200746


"Arun" wrote:

Instead of defining Cars by simply selecting the range and typing "Cars" into
the title bar, go to InsertNameDefine and in the Name box type in "Cars2"
then in the 'refers to' bar type in "=offset(B2,0,0,3,3)" then select Add.

Now replace the text in A2 with "Cars2". I find it doesn't work despite the
fact that it is still a legally defined range.

"Gary''s Student" wrote:

Give me a more specific example.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Yes, I think that this will work well - except that it does not seem to work
at all if the range is dynamic. I have the range defined as a formula using
the InsertNameDefine menu (it does not appear on the drop down list in the
menu bar). Do you find this to be the case also?

"Gary''s Student" wrote:

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can index any of
a set of tables simply by storing the table name in a reference cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call a dynamic range
name from another cell? I have a dynamic range named Cars. Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

I think you are correct. I don't know a way around this.
--
Gary''s Student - gsnu200746


"Arun" wrote:

That's because it's a range and not a single value. If you entered the
formula '=cars' in the cell you would also get the #VALUE! error - yet this
range name works in the INDIRECT function as you showed earlier.

The reason that B2 highlights is that it is the cell value specified in your
formula. This issue is really with the INDIRECT function and not with the
INDEX function. If you remove the INDIRECT part, the INDEX function works
perfectly well regardless of how you define the range (using cars or cars2).

"Gary''s Student" wrote:

Now we are getting somewhere.

Take you equation
=offset(B2,0,0,3,3)

and insert it in a cell and it displays:
#VALUE!

What is more important is that if you click in the cell with the formula,
you will see it refers to a single cell: B2 rather than a small table.
Maybe this annoys INDEX()??
--
Gary''s Student - gsnu200746


"Arun" wrote:

Instead of defining Cars by simply selecting the range and typing "Cars" into
the title bar, go to InsertNameDefine and in the Name box type in "Cars2"
then in the 'refers to' bar type in "=offset(B2,0,0,3,3)" then select Add.

Now replace the text in A2 with "Cars2". I find it doesn't work despite the
fact that it is still a legally defined range.

"Gary''s Student" wrote:

Give me a more specific example.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Yes, I think that this will work well - except that it does not seem to work
at all if the range is dynamic. I have the range defined as a formula using
the InsertNameDefine menu (it does not appear on the drop down list in the
menu bar). Do you find this to be the case also?

"Gary''s Student" wrote:

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can index any of
a set of tables simply by storing the table name in a reference cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call a dynamic range
name from another cell? I have a dynamic range named Cars. Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

The problem is that INDIRECT requires a TEXT representation of a valid
reference. A *named formula* is not a TEXT representation of a valid
reference.

How are you using INDIRECT? You can always "build" the dynamic range
directly into the formula instead of trying to call it up with INDIRECT.

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
I think you are correct. I don't know a way around this.
--
Gary''s Student - gsnu200746


"Arun" wrote:

That's because it's a range and not a single value. If you entered the
formula '=cars' in the cell you would also get the #VALUE! error - yet
this
range name works in the INDIRECT function as you showed earlier.

The reason that B2 highlights is that it is the cell value specified in
your
formula. This issue is really with the INDIRECT function and not with
the
INDEX function. If you remove the INDIRECT part, the INDEX function
works
perfectly well regardless of how you define the range (using cars or
cars2).

"Gary''s Student" wrote:

Now we are getting somewhere.

Take you equation
=offset(B2,0,0,3,3)

and insert it in a cell and it displays:
#VALUE!

What is more important is that if you click in the cell with the
formula,
you will see it refers to a single cell: B2 rather than a small table.
Maybe this annoys INDEX()??
--
Gary''s Student - gsnu200746


"Arun" wrote:

Instead of defining Cars by simply selecting the range and typing
"Cars" into
the title bar, go to InsertNameDefine and in the Name box type in
"Cars2"
then in the 'refers to' bar type in "=offset(B2,0,0,3,3)" then select
Add.

Now replace the text in A2 with "Cars2". I find it doesn't work
despite the
fact that it is still a legally defined range.

"Gary''s Student" wrote:

Give me a more specific example.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Yes, I think that this will work well - except that it does not
seem to work
at all if the range is dynamic. I have the range defined as a
formula using
the InsertNameDefine menu (it does not appear on the drop down
list in the
menu bar). Do you find this to be the case also?

"Gary''s Student" wrote:

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can
index any of
a set of tables simply by storing the table name in a reference
cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call
a dynamic range
name from another cell? I have a dynamic range named Cars.
Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined
with a formula
using Insert-Name) is there any work around?
-Arun

INDIRECT with dynamic range
 

Thanks Biff
--
Gary''s Student - gsnu200746


"T. Valko" wrote:

The problem is that INDIRECT requires a TEXT representation of a valid
reference. A *named formula* is not a TEXT representation of a valid
reference.

How are you using INDIRECT? You can always "build" the dynamic range
directly into the formula instead of trying to call it up with INDIRECT.

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
I think you are correct. I don't know a way around this.
--
Gary''s Student - gsnu200746


"Arun" wrote:

That's because it's a range and not a single value. If you entered the
formula '=cars' in the cell you would also get the #VALUE! error - yet
this
range name works in the INDIRECT function as you showed earlier.

The reason that B2 highlights is that it is the cell value specified in
your
formula. This issue is really with the INDIRECT function and not with
the
INDEX function. If you remove the INDIRECT part, the INDEX function
works
perfectly well regardless of how you define the range (using cars or
cars2).

"Gary''s Student" wrote:

Now we are getting somewhere.

Take you equation
=offset(B2,0,0,3,3)

and insert it in a cell and it displays:
#VALUE!

What is more important is that if you click in the cell with the
formula,
you will see it refers to a single cell: B2 rather than a small table.
Maybe this annoys INDEX()??
--
Gary''s Student - gsnu200746


"Arun" wrote:

Instead of defining Cars by simply selecting the range and typing
"Cars" into
the title bar, go to InsertNameDefine and in the Name box type in
"Cars2"
then in the 'refers to' bar type in "=offset(B2,0,0,3,3)" then select
Add.

Now replace the text in A2 with "Cars2". I find it doesn't work
despite the
fact that it is still a legally defined range.

"Gary''s Student" wrote:

Give me a more specific example.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Yes, I think that this will work well - except that it does not
seem to work
at all if the range is dynamic. I have the range defined as a
formula using
the InsertNameDefine menu (it does not appear on the drop down
list in the
menu bar). Do you find this to be the case also?

"Gary''s Student" wrote:

This is an excellent question!

Say in B2 thru D4 we have:

4 6 8
6 9 12
8 12 16

and we assign this block of cells the Name:
cars

You will find
=INDEX(B2:D4,1,1)
=INDEX(cars,1,1)
both work.

if A1 contains the text:
B2:D4
=INDEX(INDIRECT(A1),1,1) will also work

If A2 contains the text:
cars
=INDEX(INDIRECT(A2),1,1) will also work

The question is excellent to me because it revealed that I can
index any of
a set of tables simply by storing the table name in a reference
cell.
--
Gary''s Student - gsnu200746


"Arun" wrote:

Is it true that you cannot use the INDIRECT function to call
a dynamic range
name from another cell? I have a dynamic range named Cars.
Cell A1 has this
text in it. For some reason this formula is not working:

=Index(Indirect(A1),1,1)
although this works...
=Index(Cars,1,1)

If I can't do this because Cars is a dynamic range (defined
with a formula
using Insert-Name) is there any work around?
-Arun