| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files. Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymm dd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50") which evaluates as - D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50 In cell F10, I want to be able to incorporate a call to VLOOKUP along the following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE) When I try this, I get an #REF! error. Can anyone tell me where I'm going wrong? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
If you open that "sending" workbook, you'll see that you did everything perfect.
But =indirect() only works if the "sending" workbook is open. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Mike McLellan wrote: I want to use VLOOKUP to help produce a summary spreadsheet from a number of files. Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymm dd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50") which evaluates as - D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50 In cell F10, I want to be able to incorporate a call to VLOOKUP along the following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE) When I try this, I get an #REF! error. Can anyone tell me where I'm going wrong? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Thanks, Dave.
INDIRECT.EXT requires that the whole string is enclosed by double quotes - how do I amend formula in C10 to achieve this? "Dave Peterson" wrote: If you open that "sending" workbook, you'll see that you did everything perfect. But =indirect() only works if the "sending" workbook is open. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Mike McLellan wrote: I want to use VLOOKUP to help produce a summary spreadsheet from a number of files. Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymm dd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50") which evaluates as - D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50 In cell F10, I want to be able to incorporate a call to VLOOKUP along the following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE) When I try this, I get an #REF! error. Can anyone tell me where I'm going wrong? -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Maybe...
=VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE) Mike McLellan wrote: Thanks, Dave. INDIRECT.EXT requires that the whole string is enclosed by double quotes - how do I amend formula in C10 to achieve this? "Dave Peterson" wrote: If you open that "sending" workbook, you'll see that you did everything perfect. But =indirect() only works if the "sending" workbook is open. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Mike McLellan wrote: I want to use VLOOKUP to help produce a summary spreadsheet from a number of files. Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymm dd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50") which evaluates as - D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50 In cell F10, I want to be able to incorporate a call to VLOOKUP along the following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE) When I try this, I get an #REF! error. Can anyone tell me where I'm going wrong? -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Almost there.
I am now getting an #VALUE! error - any ideas? "Dave Peterson" wrote: Maybe... =VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE) Mike McLellan wrote: Thanks, Dave. INDIRECT.EXT requires that the whole string is enclosed by double quotes - how do I amend formula in C10 to achieve this? "Dave Peterson" wrote: If you open that "sending" workbook, you'll see that you did everything perfect. But =indirect() only works if the "sending" workbook is open. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Mike McLellan wrote: I want to use VLOOKUP to help produce a summary spreadsheet from a number of files. Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymm dd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50") which evaluates as - D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50 In cell F10, I want to be able to incorporate a call to VLOOKUP along the following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE) When I try this, I get an #REF! error. Can anyone tell me where I'm going wrong? -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
I'd open that other workbook.
Build my formula so that it works with =indirect() close the other workbook (so you see the path) and see if just changing =indirect() to =indirect.ext() would make it work. Mike McLellan wrote: Almost there. I am now getting an #VALUE! error - any ideas? "Dave Peterson" wrote: Maybe... =VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE) Mike McLellan wrote: Thanks, Dave. INDIRECT.EXT requires that the whole string is enclosed by double quotes - how do I amend formula in C10 to achieve this? "Dave Peterson" wrote: If you open that "sending" workbook, you'll see that you did everything perfect. But =indirect() only works if the "sending" workbook is open. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Mike McLellan wrote: I want to use VLOOKUP to help produce a summary spreadsheet from a number of files. Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymm dd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50") which evaluates as - D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50 In cell F10, I want to be able to incorporate a call to VLOOKUP along the following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE) When I try this, I get an #REF! error. Can anyone tell me where I'm going wrong? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Did as you suggested:
- =VLOOKUP(D11,INDIRECT(C11),3,FALSE) works - changed to =VLOOKUP(D11,INDIRECT.ext(C11),3,FALSE) - doesn't work - tried =VLOOKUP(D11,INDIRECT("""&C11&"""),3,FALSE) - doesn't work Any ideas? "Dave Peterson" wrote: I'd open that other workbook. Build my formula so that it works with =indirect() close the other workbook (so you see the path) and see if just changing =indirect() to =indirect.ext() would make it work. Mike McLellan wrote: Almost there. I am now getting an #VALUE! error - any ideas? "Dave Peterson" wrote: Maybe... =VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE) Mike McLellan wrote: Thanks, Dave. INDIRECT.EXT requires that the whole string is enclosed by double quotes - how do I amend formula in C10 to achieve this? "Dave Peterson" wrote: If you open that "sending" workbook, you'll see that you did everything perfect. But =indirect() only works if the "sending" workbook is open. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Mike McLellan wrote: I want to use VLOOKUP to help produce a summary spreadsheet from a number of files. Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymm dd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50") which evaluates as - D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50 In cell F10, I want to be able to incorporate a call to VLOOKUP along the following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE) When I try this, I get an #REF! error. Can anyone tell me where I'm going wrong? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
I used 4 double quotes in my first suggestion:
=VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE) Did that not work? Mike McLellan wrote: Did as you suggested: - =VLOOKUP(D11,INDIRECT(C11),3,FALSE) works - changed to =VLOOKUP(D11,INDIRECT.ext(C11),3,FALSE) - doesn't work - tried =VLOOKUP(D11,INDIRECT("""&C11&"""),3,FALSE) - doesn't work Any ideas? "Dave Peterson" wrote: I'd open that other workbook. Build my formula so that it works with =indirect() close the other workbook (so you see the path) and see if just changing =indirect() to =indirect.ext() would make it work. Mike McLellan wrote: Almost there. I am now getting an #VALUE! error - any ideas? "Dave Peterson" wrote: Maybe... =VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE) Mike McLellan wrote: Thanks, Dave. INDIRECT.EXT requires that the whole string is enclosed by double quotes - how do I amend formula in C10 to achieve this? "Dave Peterson" wrote: If you open that "sending" workbook, you'll see that you did everything perfect. But =indirect() only works if the "sending" workbook is open. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Mike McLellan wrote: I want to use VLOOKUP to help produce a summary spreadsheet from a number of files. Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymm dd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50") which evaluates as - D:\Documents and Settings\fzvtc6\My Documents\EDS Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50 In cell F10, I want to be able to incorporate a call to VLOOKUP along the following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE) When I try this, I get an #REF! error. Can anyone tell me where I'm going wrong? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP Problem | Excel Discussion (Misc queries) | |||
| Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
| VLOOKUP Limitations | Excel Worksheet Functions | |||
| Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
| vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Linear Mode
