Hello all,

I seem to be becoming a bit of a regular here. Thanks to everyone who has
helped me so far!

Just a quick one this time. I am trying to use INDEX to get to a named array
in a different workbook. Having found that I get a #N/A message I have
started to wonder whether this is actually possible. The formula I am using
is:

=INDEX('New orders 2001.xls'!Jan,1,7)

I had thought that the file name needed to be in square brackets and the
name of the array including between the single quotes like so:

=INDEX('[New orders 2001.xls]!Jan',1,7)

But it would not accept this as a correct formula.

I bet it is something stupid so if you can see what it is please let me know!

Thanks again.

=INDEX([New orders 2001.xls]Jan! .... ,1,7)

Put array reference in place of ...
e.g. if your array is A1:G1 in sheet Jan in workbook New orders 2001.xls,
then =INDEX([New orders 2001.xls]Jan!A1:G1,1,7) returns the content of G1
(because it is the 7th column in row 1.

Regards,
Stefi

€žGinger€� ezt Ã*rta:

Hello all,

I seem to be becoming a bit of a regular here. Thanks to everyone who has
helped me so far!

Just a quick one this time. I am trying to use INDEX to get to a named array
in a different workbook. Having found that I get a #N/A message I have
started to wonder whether this is actually possible. The formula I am using
is:

=INDEX('New orders 2001.xls'!Jan,1,7)

I had thought that the file name needed to be in square brackets and the
name of the array including between the single quotes like so:

=INDEX('[New orders 2001.xls]!Jan',1,7)

But it would not accept this as a correct formula.

I bet it is something stupid so if you can see what it is please let me know!

Thanks again.

I have had success in calling up manually defined arrays in sheets in other
workbooks. What I can't seem to get to work is using a named set of values by
using INSERTNAMEDEFINE. I have discovered that you can have a name in the
current workbook for an array in a different workbook but what I was really
hoping to be able to do was refer to a name that is already set up in another
workbook. The problem is I cannot figure out how to define it without making
it look like a reference to a sheet as opposed to a named array.

So I have tried to work around the problem by naming the arrays in the
current workbook but with the arrays themselves in the external workbook but
I still cannot reference to these using INDEX. I have had working

=INDEX(January,$D3,E$1)

where January is an array in another workbook but named in the current one.
D3 and E1 are row and column numbers. I want to have A3 instead of January
and have the month in A3 but instead of referring to the named array it just
writes "January" in the cell. If I can do this then I can use AutoFill to
complete everything without me having to update formulae as time passes.

"Stefi" wrote:

=INDEX([New orders 2001.xls]Jan! .... ,1,7)

Put array reference in place of ...
e.g. if your array is A1:G1 in sheet Jan in workbook New orders 2001.xls,
then =INDEX([New orders 2001.xls]Jan!A1:G1,1,7) returns the content of G1
(because it is the 7th column in row 1.

Regards,
Stefi

€žGinger€� ezt Ã*rta:

Hello all,

I seem to be becoming a bit of a regular here. Thanks to everyone who has
helped me so far!

Just a quick one this time. I am trying to use INDEX to get to a named array
in a different workbook. Having found that I get a #N/A message I have
started to wonder whether this is actually possible. The formula I am using
is:

=INDEX('New orders 2001.xls'!Jan,1,7)

I had thought that the file name needed to be in square brackets and the
name of the array including between the single quotes like so:

=INDEX('[New orders 2001.xls]!Jan',1,7)

But it would not accept this as a correct formula.

I bet it is something stupid so if you can see what it is please let me know!

Thanks again.

Hi,

Please try to include the path...

I'm using =Index('C:\Temp\Book1.xls'!Jan, 1,7) which Jan is the named
range in Book1.xls without any error.



"Ginger" wrote in message
...
I have had success in calling up manually defined arrays in sheets in other
workbooks. What I can't seem to get to work is using a named set of values
by
using INSERTNAMEDEFINE. I have discovered that you can have a name in the
current workbook for an array in a different workbook but what I was really
hoping to be able to do was refer to a name that is already set up in
another
workbook. The problem is I cannot figure out how to define it without making
it look like a reference to a sheet as opposed to a named array.

So I have tried to work around the problem by naming the arrays in the
current workbook but with the arrays themselves in the external workbook but
I still cannot reference to these using INDEX. I have had working

=INDEX(January,$D3,E$1)

where January is an array in another workbook but named in the current one.
D3 and E1 are row and column numbers. I want to have A3 instead of January
and have the month in A3 but instead of referring to the named array it just
writes "January" in the cell. If I can do this then I can use AutoFill to
complete everything without me having to update formulae as time passes.

"Stefi" wrote:

=INDEX([New orders 2001.xls]Jan! .... ,1,7)

Put array reference in place of ...
e.g. if your array is A1:G1 in sheet Jan in workbook New orders 2001.xls,
then =INDEX([New orders 2001.xls]Jan!A1:G1,1,7) returns the content of G1
(because it is the 7th column in row 1.

Regards,
Stefi

"Ginger" ezt írta:

Hello all,

I seem to be becoming a bit of a regular here. Thanks to everyone who
has
helped me so far!

Just a quick one this time. I am trying to use INDEX to get to a named
array
in a different workbook. Having found that I get a #N/A message I have
started to wonder whether this is actually possible. The formula I am
using
is:

=INDEX('New orders 2001.xls'!Jan,1,7)

I had thought that the file name needed to be in square brackets and the
name of the array including between the single quotes like so:

=INDEX('[New orders 2001.xls]!Jan',1,7)

But it would not accept this as a correct formula.

I bet it is something stupid so if you can see what it is please let me
know!

Thanks again.

Hi,

Thanks for the reply. I have tried exactly this (changing the bits that
needed changing to make it work on my spreadsheet) and it didn't work. I am
really looking at going a stage beyond this anyway so that I can put this
information in a separate cell and then call it in, so for example I want to
have A3 rather than Jan, which would allow me to have file references which
automatically updated themselves.

"kk" wrote:

Hi,

Please try to include the path...

I'm using =Index('C:\Temp\Book1.xls'!Jan, 1,7) which Jan is the named
range in Book1.xls without any error.



"Ginger" wrote in message
...
I have had success in calling up manually defined arrays in sheets in other
workbooks. What I can't seem to get to work is using a named set of values
by
using INSERTNAMEDEFINE. I have discovered that you can have a name in the
current workbook for an array in a different workbook but what I was really
hoping to be able to do was refer to a name that is already set up in
another
workbook. The problem is I cannot figure out how to define it without making
it look like a reference to a sheet as opposed to a named array.

So I have tried to work around the problem by naming the arrays in the
current workbook but with the arrays themselves in the external workbook but
I still cannot reference to these using INDEX. I have had working

=INDEX(January,$D3,E$1)

where January is an array in another workbook but named in the current one.
D3 and E1 are row and column numbers. I want to have A3 instead of January
and have the month in A3 but instead of referring to the named array it just
writes "January" in the cell. If I can do this then I can use AutoFill to
complete everything without me having to update formulae as time passes.

"Stefi" wrote:

=INDEX([New orders 2001.xls]Jan! .... ,1,7)

Put array reference in place of ...
e.g. if your array is A1:G1 in sheet Jan in workbook New orders 2001.xls,
then =INDEX([New orders 2001.xls]Jan!A1:G1,1,7) returns the content of G1
(because it is the 7th column in row 1.

Regards,
Stefi

"Ginger" ezt Ã*rta:

Hello all,

I seem to be becoming a bit of a regular here. Thanks to everyone who
has
helped me so far!

Just a quick one this time. I am trying to use INDEX to get to a named
array
in a different workbook. Having found that I get a #N/A message I have
started to wonder whether this is actually possible. The formula I am
using
is:

=INDEX('New orders 2001.xls'!Jan,1,7)

I had thought that the file name needed to be in square brackets and the
name of the array including between the single quotes like so:

=INDEX('[New orders 2001.xls]!Jan',1,7)

But it would not accept this as a correct formula.

I bet it is something stupid so if you can see what it is please let me
know!

Thanks again.

I found this workaround:

Run this simple one-line macro when you want to insert the INDEX formula:

Select the cell you want to insert to!

Sub InsertIndex()
ActiveCell.FormulaR1C1 =
"=INDEX('New orders 2001.xls'!" & Range("A3") & ",R[2]C[3],RC[4])"
End Sub

It picks up the name of the month from A3.

R[2]C[3] refers to D3
RC[4]) refers to E1 the Activecell being A1 in this example.

Regards,
Stefi

€žGinger€� ezt Ã*rta:

I have had success in calling up manually defined arrays in sheets in other
workbooks. What I can't seem to get to work is using a named set of values by
using INSERTNAMEDEFINE. I have discovered that you can have a name in the
current workbook for an array in a different workbook but what I was really
hoping to be able to do was refer to a name that is already set up in another
workbook. The problem is I cannot figure out how to define it without making
it look like a reference to a sheet as opposed to a named array.

So I have tried to work around the problem by naming the arrays in the
current workbook but with the arrays themselves in the external workbook but
I still cannot reference to these using INDEX. I have had working

=INDEX(January,$D3,E$1)

where January is an array in another workbook but named in the current one.
D3 and E1 are row and column numbers. I want to have A3 instead of January
and have the month in A3 but instead of referring to the named array it just
writes "January" in the cell. If I can do this then I can use AutoFill to
complete everything without me having to update formulae as time passes.

"Stefi" wrote:

=INDEX([New orders 2001.xls]Jan! .... ,1,7)

Put array reference in place of ...
e.g. if your array is A1:G1 in sheet Jan in workbook New orders 2001.xls,
then =INDEX([New orders 2001.xls]Jan!A1:G1,1,7) returns the content of G1
(because it is the 7th column in row 1.

Regards,
Stefi

€žGinger€� ezt Ã*rta:

Hello all,

I seem to be becoming a bit of a regular here. Thanks to everyone who has
helped me so far!

Just a quick one this time. I am trying to use INDEX to get to a named array
in a different workbook. Having found that I get a #N/A message I have
started to wonder whether this is actually possible. The formula I am using
is:

=INDEX('New orders 2001.xls'!Jan,1,7)

I had thought that the file name needed to be in square brackets and the
name of the array including between the single quotes like so:

=INDEX('[New orders 2001.xls]!Jan',1,7)

But it would not accept this as a correct formula.

I bet it is something stupid so if you can see what it is please let me know!

Thanks again.