Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vairable Fields in a VLookup
I am using a simple Vlookup below:
=VLOOKUP(A58,[a74]Task_Export_Table!$C$1:$J$200,4,FALSE) This lookup is in spreadsheet A. It references data from spreadsheet B, a different spreadsheet located on my hard drive. Because the spreadsheets will move to other computers/hard drives, I want to allow people to specify the location of B: I'm using [a74], which includes the drive location (P:\Testing\etc.) in the vlookup to accomplish this. Unfortunately, if I change the value in a74, the vlookup doesn't reference the new value but it appears that it references the original value in A74. Any help would be appreciated. Paul C. Peterson. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vairable Fields in a VLookup
Can you pl. tell me how [a74] works? I have not seen this before...
For your problem try INDIRECT to build the reference to the table. "Paul Peterson - Velox Consulting, LLC" wrote: I am using a simple Vlookup below: =VLOOKUP(A58,[a74]Task_Export_Table!$C$1:$J$200,4,FALSE) This lookup is in spreadsheet A. It references data from spreadsheet B, a different spreadsheet located on my hard drive. Because the spreadsheets will move to other computers/hard drives, I want to allow people to specify the location of B: I'm using [a74], which includes the drive location (P:\Testing\etc.) in the vlookup to accomplish this. Unfortunately, if I change the value in a74, the vlookup doesn't reference the new value but it appears that it references the original value in A74. Any help would be appreciated. Paul C. Peterson. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vairable Fields in a VLookup
The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Paul Peterson - Velox Consulting, LLC wrote: I am using a simple Vlookup below: =VLOOKUP(A58,[a74]Task_Export_Table!$C$1:$J$200,4,FALSE) This lookup is in spreadsheet A. It references data from spreadsheet B, a different spreadsheet located on my hard drive. Because the spreadsheets will move to other computers/hard drives, I want to allow people to specify the location of B: I'm using [a74], which includes the drive location (P:\Testing\etc.) in the vlookup to accomplish this. Unfortunately, if I change the value in a74, the vlookup doesn't reference the new value but it appears that it references the original value in A74. Any help would be appreciated. Paul C. Peterson. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vairable Fields in a VLookup
Thanks - the A74 is the cell location for the directory structure that
references the external spreadsheet. Depending on who is using the spreadsheet, the value in that cell will change to a different location. "Sheeloo" wrote: Can you pl. tell me how [a74] works? I have not seen this before... For your problem try INDIRECT to build the reference to the table. "Paul Peterson - Velox Consulting, LLC" wrote: I am using a simple Vlookup below: =VLOOKUP(A58,[a74]Task_Export_Table!$C$1:$J$200,4,FALSE) This lookup is in spreadsheet A. It references data from spreadsheet B, a different spreadsheet located on my hard drive. Because the spreadsheets will move to other computers/hard drives, I want to allow people to specify the location of B: I'm using [a74], which includes the drive location (P:\Testing\etc.) in the vlookup to accomplish this. Unfortunately, if I change the value in a74, the vlookup doesn't reference the new value but it appears that it references the original value in A74. Any help would be appreciated. Paul C. Peterson. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vairable Fields in a VLookup
Thanks, Dave. I will try the indirect and the addin.
"Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Paul Peterson - Velox Consulting, LLC wrote: I am using a simple Vlookup below: =VLOOKUP(A58,[a74]Task_Export_Table!$C$1:$J$200,4,FALSE) This lookup is in spreadsheet A. It references data from spreadsheet B, a different spreadsheet located on my hard drive. Because the spreadsheets will move to other computers/hard drives, I want to allow people to specify the location of B: I'm using [a74], which includes the drive location (P:\Testing\etc.) in the vlookup to accomplish this. Unfortunately, if I change the value in a74, the vlookup doesn't reference the new value but it appears that it references the original value in A74. Any help would be appreciated. Paul C. Peterson. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to look up multiple fields? | Excel Discussion (Misc queries) | |||
vlookup 2 fields | Excel Worksheet Functions | |||
vlookup 2 fields | Excel Worksheet Functions | |||
Vlookup for non-unique fields | Excel Discussion (Misc queries) | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions |