Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index,Match table array in separate workbook
Can the table array of an index/match type lookup reside in a separate
workbook without any problems |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index,Match table array in separate workbook
Yes it can: no problem!
Didn't you try? HTH -- AP "Ben" a écrit dans le message de news: ... Can the table array of an index/match type lookup reside in a separate workbook without any problems |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index,Match table array in separate workbook
I did but I exprienced some problems with the links. Now that I know it's OK
I'll try again But I have a further question please. Can the name of the other workbook reside in a cell in the workbook that contains the lookup formula. If so could you please post an example of a simple Vlookup substituting the Table Array with a reference to a cell that contains either the full path of the array or maybe just the name of the other workbook. I have experimented with it but I have not been successful so far. Thanks "Ardus Petus" wrote: Yes it can: no problem! Didn't you try? HTH -- AP "Ben" a écrit dans le message de news: ... Can the table array of an index/match type lookup reside in a separate workbook without any problems |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index,Match table array in separate workbook
Here is what I mean
=VLOOKUP(M5,[Copper.xls]Prices!$B$3:$Z$1892,6) I'd like the reference to the other workbook Copper.xls to reside in a cell a5. How should I then express this formula. Thanks "Ben" wrote: I did but I exprienced some problems with the links. Now that I know it's OK I'll try again But I have a further question please. Can the name of the other workbook reside in a cell in the workbook that contains the lookup formula. If so could you please post an example of a simple Vlookup substituting the Table Array with a reference to a cell that contains either the full path of the array or maybe just the name of the other workbook. I have experimented with it but I have not been successful so far. Thanks "Ardus Petus" wrote: Yes it can: no problem! Didn't you try? HTH -- AP "Ben" a écrit dans le message de news: ... Can the table array of an index/match type lookup reside in a separate workbook without any problems |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index,Match table array in separate workbook
Thanks, that helped but for the benefit of future readers it did not work
first time. I think there may be 2 slight inaccuracies in the formula (there's a qoute mark missing after1892 and I believe the last semi colon should be a comma. The syntax below works fine. Thanks for your help. =VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892"),6,FALSE) "Ardus Petus" wrote: =VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892),6;FALSE) You probably forgot to specify VLOOKUP 4th parameter. HTH -- AP "Ben" a écrit dans le message de news: ... Here is what I mean =VLOOKUP(M5,[Copper.xls]Prices!$B$3:$Z$1892,6) I'd like the reference to the other workbook Copper.xls to reside in a cell a5. How should I then express this formula. Thanks "Ben" wrote: I did but I exprienced some problems with the links. Now that I know it's OK I'll try again But I have a further question please. Can the name of the other workbook reside in a cell in the workbook that contains the lookup formula. If so could you please post an example of a simple Vlookup substituting the Table Array with a reference to a cell that contains either the full path of the array or maybe just the name of the other workbook. I have experimented with it but I have not been successful so far. Thanks "Ardus Petus" wrote: Yes it can: no problem! Didn't you try? HTH -- AP "Ben" a écrit dans le message de news: ... Can the table array of an index/match type lookup reside in a separate workbook without any problems |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index,Match table array in separate workbook
Thanks for the feedback
I answered your question without testing. Semi-colon is my french locale separator. Cheers, -- AP "Ben" a écrit dans le message de news: ... Thanks, that helped but for the benefit of future readers it did not work first time. I think there may be 2 slight inaccuracies in the formula (there's a qoute mark missing after1892 and I believe the last semi colon should be a comma. The syntax below works fine. Thanks for your help. =VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892"),6,FALSE) "Ardus Petus" wrote: =VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892),6;FALSE) You probably forgot to specify VLOOKUP 4th parameter. HTH -- AP "Ben" a écrit dans le message de news: ... Here is what I mean =VLOOKUP(M5,[Copper.xls]Prices!$B$3:$Z$1892,6) I'd like the reference to the other workbook Copper.xls to reside in a cell a5. How should I then express this formula. Thanks "Ben" wrote: I did but I exprienced some problems with the links. Now that I know it's OK I'll try again But I have a further question please. Can the name of the other workbook reside in a cell in the workbook that contains the lookup formula. If so could you please post an example of a simple Vlookup substituting the Table Array with a reference to a cell that contains either the full path of the array or maybe just the name of the other workbook. I have experimented with it but I have not been successful so far. Thanks "Ardus Petus" wrote: Yes it can: no problem! Didn't you try? HTH -- AP "Ben" a écrit dans le message de news: ... Can the table array of an index/match type lookup reside in a separate workbook without any problems |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table with protect workbook | Excel Discussion (Misc queries) | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Pivot table using different worksheets/same workbook | Charts and Charting in Excel | |||
Table Array in VLOOKUP Relies on Data Validation | Excel Worksheet Functions | |||
How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) |