Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect inside a vlookup
Hello,
I am trying to do a Vlookup that would automatically refer to a different sheet in a workbook. I have a workbook called January08.xlxs with 31 sheets called 1-1 to 1-31 respectivally. I would like to do a vlookup on cell D7 and have it return the contents of sheet 1-1in the D8 cell and would like the contents of sheet 1-2 in cell D9. In a normal vlookup it looks as follows =VLOOKUP(D7,'[January08.xlsx]1-1'!$A$6:$F$64,2,FALSE) in cell D8. I would like to automaticcaly reference the next work sheet in the next row to have the formula =VLOOKUP(D7,'[January08.xlsx]1-2'!$A$6:$F$64,2,FALSE) in Cell D9 and so forth. I put a reference in column A with the sheet reference in each row. Cell A8 has the text of 1-1, cell A9 has 1-2 and cell A38 has 1-31.I tried using the INDIRECT function inside the Vlookup but have had no luck. The formula I tried was =VLOOKUP(D7,INDIRECT("[January08.xlxs]"&A8&"!$A6:$F64"),2,FALSE) Any help would be greatly appreciated. Thanks chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect inside a vlookup
Check you file name in your formula. In one formula you have the extention
as ".xlsx", and then in the another you have as ".xlxs". In my formula below, I'm assuming it is supposed to be ".xls". Additionally, your formula appeard to be missing the apostrophe before the open bracket "[" and before the exclamation mark "!". =VLOOKUP(D7,INDIRECT("'[January08.xls]" & A8 & "'!$A$6:$F$64"),2,FALSE) It appears that your only returning a value in column 2 of your table. So why not limit your range to two columns? =VLOOKUP(D7,INDIRECT("'[January08.xls]" & A8 & "'!$A$6:$B$64"),2,FALSE) HTH, Paul -- "Diggsy" wrote in message ... Hello, I am trying to do a Vlookup that would automatically refer to a different sheet in a workbook. I have a workbook called January08.xlxs with 31 sheets called 1-1 to 1-31 respectivally. I would like to do a vlookup on cell D7 and have it return the contents of sheet 1-1in the D8 cell and would like the contents of sheet 1-2 in cell D9. In a normal vlookup it looks as follows =VLOOKUP(D7,'[January08.xlsx]1-1'!$A$6:$F$64,2,FALSE) in cell D8. I would like to automaticcaly reference the next work sheet in the next row to have the formula =VLOOKUP(D7,'[January08.xlsx]1-2'!$A$6:$F$64,2,FALSE) in Cell D9 and so forth. I put a reference in column A with the sheet reference in each row. Cell A8 has the text of 1-1, cell A9 has 1-2 and cell A38 has 1-31.I tried using the INDIRECT function inside the Vlookup but have had no luck. The formula I tried was =VLOOKUP(D7,INDIRECT("[January08.xlxs]"&A8&"!$A6:$F64"),2,FALSE) Any help would be greatly appreciated. Thanks chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect inside a vlookup
Thank You very Much.My mistake...Actually the the worksheet extension was
xlxs. It works great Thank you. I needed a larger range because I am looking at many colums with the vlookup. I was just referencing the 2nd cloumn (The B column) as just a part of the example. Thanks Again "PCLIVE" wrote: Check you file name in your formula. In one formula you have the extention as ".xlsx", and then in the another you have as ".xlxs". In my formula below, I'm assuming it is supposed to be ".xls". Additionally, your formula appeard to be missing the apostrophe before the open bracket "[" and before the exclamation mark "!". =VLOOKUP(D7,INDIRECT("'[January08.xls]" & A8 & "'!$A$6:$F$64"),2,FALSE) It appears that your only returning a value in column 2 of your table. So why not limit your range to two columns? =VLOOKUP(D7,INDIRECT("'[January08.xls]" & A8 & "'!$A$6:$B$64"),2,FALSE) HTH, Paul -- "Diggsy" wrote in message ... Hello, I am trying to do a Vlookup that would automatically refer to a different sheet in a workbook. I have a workbook called January08.xlxs with 31 sheets called 1-1 to 1-31 respectivally. I would like to do a vlookup on cell D7 and have it return the contents of sheet 1-1in the D8 cell and would like the contents of sheet 1-2 in cell D9. In a normal vlookup it looks as follows =VLOOKUP(D7,'[January08.xlsx]1-1'!$A$6:$F$64,2,FALSE) in cell D8. I would like to automaticcaly reference the next work sheet in the next row to have the formula =VLOOKUP(D7,'[January08.xlsx]1-2'!$A$6:$F$64,2,FALSE) in Cell D9 and so forth. I put a reference in column A with the sheet reference in each row. Cell A8 has the text of 1-1, cell A9 has 1-2 and cell A38 has 1-31.I tried using the INDIRECT function inside the Vlookup but have had no luck. The formula I tried was =VLOOKUP(D7,INDIRECT("[January08.xlxs]"&A8&"!$A6:$F64"),2,FALSE) Any help would be greatly appreciated. Thanks chris |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect inside a vlookup
Your welcome.
-- "Diggsy" wrote in message ... Thank You very Much.My mistake...Actually the the worksheet extension was xlxs. It works great Thank you. I needed a larger range because I am looking at many colums with the vlookup. I was just referencing the 2nd cloumn (The B column) as just a part of the example. Thanks Again "PCLIVE" wrote: Check you file name in your formula. In one formula you have the extention as ".xlsx", and then in the another you have as ".xlxs". In my formula below, I'm assuming it is supposed to be ".xls". Additionally, your formula appeard to be missing the apostrophe before the open bracket "[" and before the exclamation mark "!". =VLOOKUP(D7,INDIRECT("'[January08.xls]" & A8 & "'!$A$6:$F$64"),2,FALSE) It appears that your only returning a value in column 2 of your table. So why not limit your range to two columns? =VLOOKUP(D7,INDIRECT("'[January08.xls]" & A8 & "'!$A$6:$B$64"),2,FALSE) HTH, Paul -- "Diggsy" wrote in message ... Hello, I am trying to do a Vlookup that would automatically refer to a different sheet in a workbook. I have a workbook called January08.xlxs with 31 sheets called 1-1 to 1-31 respectivally. I would like to do a vlookup on cell D7 and have it return the contents of sheet 1-1in the D8 cell and would like the contents of sheet 1-2 in cell D9. In a normal vlookup it looks as follows =VLOOKUP(D7,'[January08.xlsx]1-1'!$A$6:$F$64,2,FALSE) in cell D8. I would like to automaticcaly reference the next work sheet in the next row to have the formula =VLOOKUP(D7,'[January08.xlsx]1-2'!$A$6:$F$64,2,FALSE) in Cell D9 and so forth. I put a reference in column A with the sheet reference in each row. Cell A8 has the text of 1-1, cell A9 has 1-2 and cell A38 has 1-31.I tried using the INDIRECT function inside the Vlookup but have had no luck. The formula I tried was =VLOOKUP(D7,INDIRECT("[January08.xlxs]"&A8&"!$A6:$F64"),2,FALSE) Any help would be greatly appreciated. Thanks chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you use HLOOKUP inside VLOOKUP to tell it what column 2 return | Excel Worksheet Functions | |||
Indirect address inside "" | Setting up and Configuration of Excel | |||
vlookup inside an if statement? | Excel Discussion (Misc queries) | |||
vlookup inside an if statement? | Excel Worksheet Functions | |||
Using Concatenate inside a vlookup | Excel Worksheet Functions |