Indirect inside a vlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Diggsy - 10 Mar 2008 15:48 GMT

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 to this Message

PCLIVE - 10 Mar 2008 16:04 GMT

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
[quoted text clipped - 18 lines]
>
> chris

Reply to this Message

Diggsy - 10 Mar 2008 16:29 GMT

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
[quoted text clipped - 34 lines]
> >
> > chris

Reply to this Message

PCLIVE - 10 Mar 2008 16:33 GMT

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
[quoted text clipped - 54 lines]
>> >
>> > chris

Reply to this Message