Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset/match returns #value error
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. "T. Valko" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Offset/Match formula returns "#N/A" | Excel Discussion (Misc queries) | |||
OFFSET: Returns error when used to a linked file | Excel Worksheet Functions | |||
Array Offset() formula with height of 1 returns duplicates? | Excel Worksheet Functions | |||
MATCH and OFFSET | Excel Worksheet Functions | |||
Offset() returns reference, first not value (proof) | Excel Discussion (Misc queries) |