Indirect inside a vlookup

[Diggsy]

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

 

[PCLIVE]

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]

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

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

--

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

 

[PCLIVE]

Your welcome.

--

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

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

--

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