INDIRECT and VLOOKUP

[ELENA911]

Hi,

I am trying to create a formula to vlookup to another workbook. I am trying to create a template for my weekly report. Each week the workbook name will be updated with the week's new date. I want the vlookup to get updated as well. I read somewhere that INDIRECT and Vlookup can make it happen. I am unable to figure out how it works. Can someone please help me ?

Thank you

 

[sous2817]

Hello and welcome to the board!

May be worth having a read here:

http://www.mrexcel.com/forum/showthread.php?t=497455

For what it's worth, INDIRECT needs the workbook you're indirecting to to be open (if I recall correctly). At any rate, seemed to be some good information in the thread I linked above.
good luck!

 

[T. Valko]

Hi,

I am trying to create a formula to vlookup to another workbook. I am trying to create a template for my weekly report. Each week the workbook name will be updated with the week's new date. I want the vlookup to get updated as well. I read somewhere that INDIRECT and Vlookup can make it happen. I am unable to figure out how it works. Can someone please help me ?

Thank you

You can use INDIRECT however, the source file MUST be open for the formula to work.

This is usually undesireable.

If you still want to pursue this then you'll have to give us more specific info as to where all the stuff is located.

 

[ELENA911]

I have a spreadsheet opened named Market_20110503.xlsx

I have a table from A1:D18
I need this table to get populated from anothet workbook. The workbook name and location on where the workbook is located is in G1

="'T:\Market_Weekly_Reports\2011\effective_date_"&TEXT(I1,"yyyymmdd")&"\[Report_effDate"&TEXT(I1,"yyyymmdd")&".xlsx]Validation"

Date to use is in H1 = example : 05/03/2011
I need to update the date weekly

the formula i have is

=VLOOKUP(A2,INDIRECT(G1,$F$1:$I$18),3,FALSE)

this produces #value error


any ideas why???


Any help would be brilliant and very welcome

 

[sous2817]

Does it work when the file you're referencing is open?

 

[T. Valko]

I have a spreadsheet opened named Market_20110503.xlsx

I have a table from A1:D18
I need this table to get populated from anothet workbook. The workbook name and location on where the workbook is located is in G1

="'T:\Market_Weekly_Reports\2011\effective_date_"&TEXT(I1,"yyyymmdd")&"\[Report_effDate"&TEXT(I1,"yyyymmdd")&".xlsx]Validation"

Date to use is in H1 = example : 05/03/2011
I need to update the date weekly

the formula i have is

=VLOOKUP(A2,INDIRECT(G1,$F$1:$I$18),3,FALSE)

this produces #value error


any ideas why???


Any help would be brilliant and very welcome

The file that's named in cell G1 MUST be open.

And, since that file MUST be open you don't need the path, just the file name name will do.

So, let's assume G1 contains this formula:

="Report_effDate"&TEXT(I1,"yyyymmdd")&".xlsx"

Let's assume the lookup table is in that file on sheet Validation in the range F1:I18.

=VLOOKUP(A2,INDIRECT("'["&G1&"]Validation'!F1:I18"),3,0)

 

[ELENA911]

It works if the spreadsheet is open. What if I want to avoid having the spreadsheet open is there away around it?

 

[Walky]

Hi,

I'm astonished that the solution to access a closed workbook via formula seems so unknown... about a year ago, I searched the web thoroughly using all sorts of key words in order to find a solution I was sure existed and my excessive patience paid-off.

You simply have to add a "plus" sign "+" at the beginning of your complete path and you're done!

I don't remember on which site I found it, but I was so glad when it worked, it avoided me having to open approx. 10 workbooks.

I use it at work on Excel 2003, so if your trials do not work, I'll try to check this thread during the day and help you out.

The paths I use are made-up by concatenating (using "&") all sorts of data so your dream can come true.

I was however recently told that there could sometimes be an update/refresh problem for the source data, but it only happened to me once. Prior to that, I added a macro that indicates the date and time at which the workbook which is fed by the closed workbooks was last refreshed.

So if Excel is in manual update, the current date & time will not show. I'll also send you that macro when I have time tomorrow.

Regards,

W.

 

[T. Valko]

It works if the spreadsheet is open. What if I want to avoid having the spreadsheet open is there away around it?

What version of Excel are you using?

There is an add-in available that has a function that can do this without the source file needing to be opened. However, this add-in has a couple of bugs in it when using it in Excel 2007. I'm kind of reluctant to suggest it for that reason but there you go...

Other than that, I don't have any other suggestions.

 

[T. Valko]

Hi,

I'm astonished that the solution to access a closed workbook via formula seems so unknown... about a year ago, I searched the web thoroughly using all sorts of key words in order to find a solution I was sure existed and my excessive patience paid-off.

You simply have to add a "plus" sign "+" at the beginning of your complete path and you're done!

I don't remember on which site I found it, but I was so glad when it worked, it avoided me having to open approx. 10 workbooks.

I use it at work on Excel 2003, so if your trials do not work, I'll try to check this thread during the day and help you out.

The paths I use are made-up by concatenating (using "&") all sorts of data so your dream can come true.

I was however recently told that there could sometimes be an update/refresh problem for the source data, but it only happened to me once. Prior to that, I added a macro that indicates the date and time at which the workbook which is fed by the closed workbooks was last refreshed.

So if Excel is in manual update, the current date & time will not show. I'll also send you that macro when I have time tomorrow.

Regards,

W.

Hmmm...

I'd love to see this work...

I have my doubts and don't think that simply adding a + (plus sign) to the path will do what the OP is wanting to do.

Here's your chance to prove me wrong!