Offset/match returns #value error

[Geoff]

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?

 

[Dave]

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.

 

[T. Valko]

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.

 

[Geoff]

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.

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.

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?

 

[Geoff]

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.

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

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?

 

[Geoff]

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.

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.

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

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?

 

[Geoff]

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.

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.

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.

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


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?

 

[T. Valko]

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

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.

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.

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.


:

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


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.

 

[Geoff]

Thanks again Biff.

Yes the formula in the original post works fine using INDEX instead of
OFFSET. The other formulas come from elsewhere in the workbook and were
likewise returning #VALUE! from the OFFSET function - the difference was that
they had specified height and width arguments to OFFSET, and so returned an
array of cells.

What I was doing in my last post was building an array using INDEX as follows:

INDEX(Array, Row_1, Column_1):INDEX(Array, Row_M, Column_N)

to return an M x N array of values (In that particular case I used MATCH to
determine Row_1 and then MATCH + 6 to return Row_7). This worked fine as long
as the workbook Array comes from was open, but when it was closed, I got the
#REF! error. The interesting thing is that when INDEX is used to return a
single value it doesn't matter whether the workbook is open or closed. I'll
keep working on it but in the end I may just have to use the original
functions, calculate and then copy/paste values. Not ideal but in the
interest of getting a result it may be necessary.

Thanks for your responses.
--
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

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.

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.


:

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.


:

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


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.