Hi,

Just a quick question about index. Am I able to have a formula as the array
in index?

Using the following as an example:

1 C E F G H
Column 1 2
3
2 Month and year Row Region 1 Region 2 Region 3
3 January2001 1
4 January2001 2
.....
203 February2001 201
.....
403 March2001 401
.....
September2005

Why can I not say for cell F3 (trying to keep things simple):

=INDEX($C$1,$E3+2,1)

or this for F5 (trying to avoid using a plain cell reference for the array):

=INDEX(INDEX($C:$C,CELL("row"),1),$E5,F$1)

The idea is to have the array names stored in column C and reference to them
as required. Each name then has a set of values associated with it using the
INSERTNAMEDEFINE menu.

Perhaps it's just not possible or perhaps I'm just not typing it in
correctly. If you can tell me which of these it is it would be very grateful!

Many thanks.

the first entry in iindex needs to be an array. the the other references
relate to ther top left cell of the array but must lie within the array.
"Ginger" wrote:

Hi,

Just a quick question about index. Am I able to have a formula as the array
in index?

Using the following as an example:

1 C E F G H
Column 1 2
3
2 Month and year Row Region 1 Region 2 Region 3
3 January2001 1
4 January2001 2
....
203 February2001 201
....
403 March2001 401
....
September2005

Why can I not say for cell F3 (trying to keep things simple):

=INDEX($C$1,$E3+2,1)

or this for F5 (trying to avoid using a plain cell reference for the array):

=INDEX(INDEX($C:$C,CELL("row"),1),$E5,F$1)

The idea is to have the array names stored in column C and reference to them
as required. Each name then has a set of values associated with it using the
INSERTNAMEDEFINE menu.

Perhaps it's just not possible or perhaps I'm just not typing it in
correctly. If you can tell me which of these it is it would be very grateful!

Many thanks.

Hi bj, thanks for the reply.

So that is why INDEX(January2001, etc works but INDEX(C3, etc does not, or
at least, not as I want it to.

OK, back to the drawing board for me then. Thanks again.

"bj" wrote:

the first entry in iindex needs to be an array. the the other references
relate to ther top left cell of the array but must lie within the array.
"Ginger" wrote:

Hi,

Just a quick question about index. Am I able to have a formula as the array
in index?

Using the following as an example:

1 C E F G H
Column 1 2
3
2 Month and year Row Region 1 Region 2 Region 3
3 January2001 1
4 January2001 2
....
203 February2001 201
....
403 March2001 401
....
September2005

Why can I not say for cell F3 (trying to keep things simple):

=INDEX($C$1,$E3+2,1)

or this for F5 (trying to avoid using a plain cell reference for the array):

=INDEX(INDEX($C:$C,CELL("row"),1),$E5,F$1)

The idea is to have the array names stored in column C and reference to them
as required. Each name then has a set of values associated with it using the
INSERTNAMEDEFINE menu.

Perhaps it's just not possible or perhaps I'm just not typing it in
correctly. If you can tell me which of these it is it would be very grateful!

Many thanks.

index does strange things. I have formulas which should not have worked ( at
least by the Help format) work and ones which should have worked not work
until I retyped them.

"Ginger" wrote:

Hi bj, thanks for the reply.

So that is why INDEX(January2001, etc works but INDEX(C3, etc does not, or
at least, not as I want it to.

OK, back to the drawing board for me then. Thanks again.

"bj" wrote:

the first entry in iindex needs to be an array. the the other references
relate to ther top left cell of the array but must lie within the array.
"Ginger" wrote:

Hi,

Just a quick question about index. Am I able to have a formula as the array
in index?

Using the following as an example:

1 C E F G H
Column 1 2
3
2 Month and year Row Region 1 Region 2 Region 3
3 January2001 1
4 January2001 2
....
203 February2001 201
....
403 March2001 401
....
September2005

Why can I not say for cell F3 (trying to keep things simple):

=INDEX($C$1,$E3+2,1)

or this for F5 (trying to avoid using a plain cell reference for the array):

=INDEX(INDEX($C:$C,CELL("row"),1),$E5,F$1)

The idea is to have the array names stored in column C and reference to them
as required. Each name then has a set of values associated with it using the
INSERTNAMEDEFINE menu.

Perhaps it's just not possible or perhaps I'm just not typing it in
correctly. If you can tell me which of these it is it would be very grateful!

Many thanks.