Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup more than one workbook
I am trying to lookup multiple workbooks in a hlookup function. Each file
name changes only by the State Abbreviation it contains data for. For example: Reimburse_Policy_Report_CO.xls Reimburse_Policy_Report_MN.xls I need to pull data from each of these 50 workbooks into one worksheet using hlookup. Column A of this dump workbook contains the abbreviation of each state. So each row would contain the data from each State Workbook. Currently my function looks like: =HLOOKUP($A6,("'C:\!Alison\Data Collection\[Reimburse_Policy_Report_"&A6&".xls]Please complete (Pharmacy)'!$C$6:$D$23"),C$1,FALSE) I assumed I could just add "$A6&" to reference each state accordingly? Is there a way to go about doing this? I have tried indirect.ext without much luck. Please help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup more than one workbook
INDIRECT will work for you, but the workbooks must be open. Please post what you've tried with INDIRECT. Here's a format that I use(the most tricky part is where to put quotes and the & signs: =VLOOKUP(A10,INDIRECT("[Soltime"&A1&".xls]"&"Sheet1!$B$5:$H$11"),5,FALSE). If you don't want the workbooks to be open but to be linked, you may need some VBA. IntricateFool Wrote: I am trying to lookup multiple workbooks in a hlookup function. Each file name changes only by the State Abbreviation it contains data for. For example: Reimburse_Policy_Report_CO.xls Reimburse_Policy_Report_MN.xls I need to pull data from each of these 50 workbooks into one worksheet using hlookup. Column A of this dump workbook contains the abbreviation of each state. So each row would contain the data from each State Workbook. Currently my function looks like: =HLOOKUP($A6,("'C:\!Alison\Data Collection\[Reimburse_Policy_Report_"&A6&".xls]Please complete (Pharmacy)'!$C$6:$D$23"),C$1,FALSE) I assumed I could just add "$A6&" to reference each state accordingly? Is there a way to go about doing this? I have tried indirect.ext without much luck. Please help. -- pikapika13 ------------------------------------------------------------------------ pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892 View this thread: http://www.excelforum.com/showthread...hreadid=548600 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup more than one workbook
You haven't replied in regards to having the workbooks open. If you
don't care if they need to be open, try removing the path. pikapika13 wrote: INDIRECT will work for you, but the workbooks must be open. Please post what you've tried with INDIRECT. Here's a format that I use(the most tricky part is where to put quotes and the & signs: =VLOOKUP(A10,INDIRECT("[Soltime"&A1&".xls]"&"Sheet1!$B$5:$H$11"),5,FALSE). If you don't want the workbooks to be open but to be linked, you may need some VBA. IntricateFool Wrote: I am trying to lookup multiple workbooks in a hlookup function. Each file name changes only by the State Abbreviation it contains data for. For example: Reimburse_Policy_Report_CO.xls Reimburse_Policy_Report_MN.xls I need to pull data from each of these 50 workbooks into one worksheet using hlookup. Column A of this dump workbook contains the abbreviation of each state. So each row would contain the data from each State Workbook. Currently my function looks like: =HLOOKUP($A6,("'C:\!Alison\Data Collection\[Reimburse_Policy_Report_"&A6&".xls]Please complete (Pharmacy)'!$C$6:$D$23"),C$1,FALSE) I assumed I could just add "$A6&" to reference each state accordingly? Is there a way to go about doing this? I have tried indirect.ext without much luck. Please help. -- pikapika13 ------------------------------------------------------------------------ pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892 View this thread: http://www.excelforum.com/showthread...hreadid=548600 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup more than one workbook
The workbooks will be closed. Is this possible without VBA? and if it isn't
possible could someone send me in the right direction as to how to go about doing this? " wrote: You haven't replied in regards to having the workbooks open. If you don't care if they need to be open, try removing the path. pikapika13 wrote: INDIRECT will work for you, but the workbooks must be open. Please post what you've tried with INDIRECT. Here's a format that I use(the most tricky part is where to put quotes and the & signs: =VLOOKUP(A10,INDIRECT("[Soltime"&A1&".xls]"&"Sheet1!$B$5:$H$11"),5,FALSE). If you don't want the workbooks to be open but to be linked, you may need some VBA. IntricateFool Wrote: I am trying to lookup multiple workbooks in a hlookup function. Each file name changes only by the State Abbreviation it contains data for. For example: Reimburse_Policy_Report_CO.xls Reimburse_Policy_Report_MN.xls I need to pull data from each of these 50 workbooks into one worksheet using hlookup. Column A of this dump workbook contains the abbreviation of each state. So each row would contain the data from each State Workbook. Currently my function looks like: =HLOOKUP($A6,("'C:\!Alison\Data Collection\[Reimburse_Policy_Report_"&A6&".xls]Please complete (Pharmacy)'!$C$6:$D$23"),C$1,FALSE) I assumed I could just add "$A6&" to reference each state accordingly? Is there a way to go about doing this? I have tried indirect.ext without much luck. Please help. -- pikapika13 ------------------------------------------------------------------------ pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892 View this thread: http://www.excelforum.com/showthread...hreadid=548600 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linking a cell to another workbook cell based on a variable name | Excel Discussion (Misc queries) | |||
Unprotect Workbook | Excel Discussion (Misc queries) | |||
Lookup worksheet name in separate workbook | Excel Discussion (Misc queries) |