Offset/match returns #value error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Geoff - 06 May 2008 22:35 GMT

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?

Signature

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

Reply to this Message

Dave - 06 May 2008 22:42 GMT

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:
>
[quoted text clipped - 5 lines]
> would like to preserve the reference to the other spreadsheet in the cells if
> at all possible. Any suggestions?

Reply to this Message

Geoff - 06 May 2008 22:53 GMT

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 :)

Signature

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

> Hi,
> One suggestion:
[quoted text clipped - 12 lines]
> > would like to preserve the reference to the other spreadsheet in the cells if
> > at all possible. Any suggestions?

Reply to this Message

T. Valko - 06 May 2008 22:46 GMT

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.

Signature

Biff
Microsoft Excel MVP

> The following function looks up a value in another workbook:
>
[quoted text clipped - 8 lines]
> if
> at all possible. Any suggestions?

Reply to this Message

Geoff - 07 May 2008 01:14 GMT

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

Signature

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

> Use INDEX instead of OFFSET.
>
[quoted text clipped - 13 lines]
> > if
> > at all possible. Any suggestions?

Reply to this Message

Geoff - 07 May 2008 01:24 GMT

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

Signature

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

> Thanks Biff
>
[quoted text clipped - 29 lines]
> > > if
> > > at all possible. Any suggestions?

Reply to this Message

Geoff - 07 May 2008 01:36 GMT

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...

Signature

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:
[quoted text clipped - 36 lines]
> > > > if
> > > > at all possible. Any suggestions?

Reply to this Message

T. Valko - 07 May 2008 02:44 GMT

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.

Signature

Biff
Microsoft Excel MVP

> Actually, this doesn't work as I thought it would - when the other
> workbook
[quoted text clipped - 52 lines]
>> > > > if
>> > > > at all possible. Any suggestions?

Reply to this Message

Geoff - 07 May 2008 03:56 GMT

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.

Signature

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.
[quoted text clipped - 61 lines]
> >> > > > if
> >> > > > at all possible. Any suggestions?

Reply to this Message