The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function returns
a #VALUE! error - so the referenced spreadsheet needs to be open for the
formula to be evaluated. I could replace all the formulae with values but I
would like to preserve the reference to the other spreadsheet in the cells if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand binary and
those who don't.

Hi,
One suggestion:
You could use a Worksheet Open procedure to open the needed workbooks each
time you open this workbook.
You could also use a Before Close procedure to close them again.
Regards - Dave.

"Geoff" wrote:

The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function returns
a #VALUE! error - so the referenced spreadsheet needs to be open for the
formula to be evaluated. I could replace all the formulae with values but I
would like to preserve the reference to the other spreadsheet in the cells if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand binary and
those who don't.

Thanks Dave
That would definitely work, but ideally I'd like to avoid the necessity of
having the other spreadsheet open at all. If it comes down to a choice of
opening the other workbook or replacing formulae with values, I'll use
values. Good thought though - thanks :)
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Dave" wrote:

Hi,
One suggestion:
You could use a Worksheet Open procedure to open the needed workbooks each
time you open this workbook.
You could also use a Before Close procedure to close them again.
Regards - Dave.

"Geoff" wrote:

The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function returns
a #VALUE! error - so the referenced spreadsheet needs to be open for the
formula to be evaluated. I could replace all the formulae with values but I
would like to preserve the reference to the other spreadsheet in the cells if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand binary and
those who don't.

Use INDEX instead of OFFSET.

I don't know how your row offset of MATCH()+4 relates to C8 but you can
probably figure it out.

--
Biff
Microsoft Excel MVP


"Geoff" wrote in message
...
The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function
returns
a #VALUE! error - so the referenced spreadsheet needs to be open for the
formula to be evaluated. I could replace all the formulae with values but
I
would like to preserve the reference to the other spreadsheet in the cells
if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand binary
and
those who don't.

Thanks Biff

This works really well in most cases. However, where OFFSET has been used to
return an array of values rather than a single value, can INDEX be used the
same way? For example, in the following formula OFFSET is used to return a
7x8 array.

=VLOOKUP("Word",OFFSET('[0208hi.xls]WKSHT_NAME'!$A$8,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0),1,7,8),8,FALSE)

I know INDEX can return an entire row or column by setting the column or row
argument to 0, but can it take say addresses of the top left and bottom right
cell of an array and then return the entire array?

Cheers
Geoff
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"T. Valko" wrote:

Use INDEX instead of OFFSET.

I don't know how your row offset of MATCH()+4 relates to C8 but you can
probably figure it out.

--
Biff
Microsoft Excel MVP


"Geoff" wrote in message
...
The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function
returns
a #VALUE! error - so the referenced spreadsheet needs to be open for the
formula to be evaluated. I could replace all the formulae with values but
I
would like to preserve the reference to the other spreadsheet in the cells
if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand binary
and
those who don't.

The answer to the question below is yes - here is the formula which replaces
the one below using INDEX instead of OFFSET:

=VLOOKUP("Word",INDEX('[0208hi.xls]WKSHT_NAME'!$B$9:$I$132,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0),1,1):INDEX('[0208hi.xls]WKSHT_NAME'!$B$9:$I$132,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0)+6,8),8,FALSE)

Cheers
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Geoff" wrote:

Thanks Biff

This works really well in most cases. However, where OFFSET has been used to
return an array of values rather than a single value, can INDEX be used the
same way? For example, in the following formula OFFSET is used to return a
7x8 array.

=VLOOKUP("Word",OFFSET('[0208hi.xls]WKSHT_NAME'!$A$8,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0),1,7,8),8,FALSE)

I know INDEX can return an entire row or column by setting the column or row
argument to 0, but can it take say addresses of the top left and bottom right
cell of an array and then return the entire array?

Cheers
Geoff
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"T. Valko" wrote:

Use INDEX instead of OFFSET.

I don't know how your row offset of MATCH()+4 relates to C8 but you can
probably figure it out.

--
Biff
Microsoft Excel MVP


"Geoff" wrote in message
...
The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function
returns
a #VALUE! error - so the referenced spreadsheet needs to be open for the
formula to be evaluated. I could replace all the formulae with values but
I
would like to preserve the reference to the other spreadsheet in the cells
if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand binary
and
those who don't.

Actually, this doesn't work as I thought it would - when the other workbook
is open this works fine, but when it's closed, the formula returns #REF!

Back to the drawing board...
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Geoff" wrote:

The answer to the question below is yes - here is the formula which replaces
the one below using INDEX instead of OFFSET:

=VLOOKUP("Word",INDEX('[0208hi.xls]WKSHT_NAME'!$B$9:$I$132,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0),1,1):INDEX('[0208hi.xls]WKSHT_NAME'!$B$9:$I$132,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0)+6,8),8,FALSE)

Cheers
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Geoff" wrote:

Thanks Biff

This works really well in most cases. However, where OFFSET has been used to
return an array of values rather than a single value, can INDEX be used the
same way? For example, in the following formula OFFSET is used to return a
7x8 array.

=VLOOKUP("Word",OFFSET('[0208hi.xls]WKSHT_NAME'!$A$8,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0),1,7,8),8,FALSE)

I know INDEX can return an entire row or column by setting the column or row
argument to 0, but can it take say addresses of the top left and bottom right
cell of an array and then return the entire array?

Cheers
Geoff
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"T. Valko" wrote:

Use INDEX instead of OFFSET.

I don't know how your row offset of MATCH()+4 relates to C8 but you can
probably figure it out.

--
Biff
Microsoft Excel MVP


"Geoff" wrote in message
...
The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function
returns
a #VALUE! error - so the referenced spreadsheet needs to be open for the
formula to be evaluated. I could replace all the formulae with values but
I
would like to preserve the reference to the other spreadsheet in the cells
if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand binary
and
those who don't.

None of these formulas look anything like the formula you posted in your
original post.

What are you trying to do?

I'm pretty sure INDEX can be used. If you want an array returned then you
find the first cell of that array and write the formula to increment the
row/column and as you copy you'll get your array.

--
Biff
Microsoft Excel MVP


"Geoff" wrote in message
...
Actually, this doesn't work as I thought it would - when the other
workbook
is open this works fine, but when it's closed, the formula returns #REF!

Back to the drawing board...
--
There are 10 types of people in the world - those who understand binary
and
those who don't.


"Geoff" wrote:

The answer to the question below is yes - here is the formula which
replaces
the one below using INDEX instead of OFFSET:

=VLOOKUP("Word",INDEX('[0208hi.xls]WKSHT_NAME'!$B$9:$I$132,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0),1,1):INDEX('[0208hi.xls]WKSHT_NAME'!$B$9:$I$132,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0)+6,8),8,FALSE)

Cheers
--
There are 10 types of people in the world - those who understand binary
and
those who don't.


"Geoff" wrote:

Thanks Biff

This works really well in most cases. However, where OFFSET has been
used to
return an array of values rather than a single value, can INDEX be used
the
same way? For example, in the following formula OFFSET is used to
return a
7x8 array.

=VLOOKUP("Word",OFFSET('[0208hi.xls]WKSHT_NAME'!$A$8,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0),1,7,8),8,FALSE)

I know INDEX can return an entire row or column by setting the column
or row
argument to 0, but can it take say addresses of the top left and bottom
right
cell of an array and then return the entire array?

Cheers
Geoff
--
There are 10 types of people in the world - those who understand binary
and
those who don't.


"T. Valko" wrote:

Use INDEX instead of OFFSET.

I don't know how your row offset of MATCH()+4 relates to C8 but you
can
probably figure it out.

--
Biff
Microsoft Excel MVP


"Geoff" wrote in message
...
The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function
returns
a #VALUE! error - so the referenced spreadsheet needs to be open
for the
formula to be evaluated. I could replace all the formulae with
values but
I
would like to preserve the reference to the other spreadsheet in
the cells
if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand
binary
and
those who don't.