VLOOKUP and INDIRECT

INDIRECT will only work with open workbooks - will the workbook to be referenced be open? How will you know the time in the timestamp, will it be within any specific period?

regards, barry

This function should work where your VLOOKUP function works (on open workbooks).

=INDIRECT(ADDRESS(6,1,1,1,"[ISS 2013-10-07-05-01-15.xls]Sheet1"))

Select allOpen in new window

You can then proceed and make the name dynamic.

=INDIRECT(ADDRESS(6,1,1,1,"[ISS " & TEXT(TODAY(),"yyyy-mm-dd")&"-"&TEXT(TIME(HOUR(NOW()),15,30),"hh-mm-ss").xls]Sheet1"))

Select allOpen in new window

Note that the syntax for the TIME() function is
=TIME([hour], [minute], [second])
I have intentionally used HOUR(NOW()) to set the [hour] value for the TIME function but hard values for minutes and seconds. You could use MINUTE(NOW()) and SECOND(NOW()) just to show the limitations. Your file name is unlikely to catch up with the name of the file. I don't think you can use wild characters. So, unless you know the seconds and can enter them as a hard value the idea probably won't fly.
You could use cell references to enter some of the values. This would read the minutes and seconds from A2 and B2.

TIME(HOUR(NOW()),A2,B1

Select allOpen in new window

Finally, one note about the TODAY() and NOW() functions. They update whenever the worksheet calculates. You may like to set calculation to manual, depending upon how quickly you want the value to change. Or you may need to give it a shove, because while nothing happens on your sheet it won't re-calculate at all.

Barry, I can open the worksheet, however I would be looking for an option to do on a closed workbook.  The report is set to run at 5 AM so far it has run within between 5:00 and 5:05.

Faustulus how does the address function work how will the correct data be pulled back to the line?

I'm talking about modifying your original function which I presume does work for you. Here it is
=VLOOKUP($A17,'[ISS 2013-10-07-05-01-15.xls]Sheet1'!$B$2:$CX$619,3,FALSE)
You can replace this part of the function with an INDIRECT function.
'[ISS 2013-10-07-05-01-15.xls]Sheet1'!$B$2:$CX$619
INDIRECT(ADDRESS(2,2,1,1,"[ISS 2013-10-07-05-01-15.xls]Sheet1")&":$CX$619")
That would look like this (for testing),
=VLOOKUP($A17,INDIRECT(ADDRESS(2,2,1,1,"[ISS 2013-10-07-05-01-15.xls]Sheet1")&":$CX$619"),3,FALSE)

Now, going back to the INDIRECT function,
INDIRECT(ADDRESS(2,2,1,1,"[ISS 2013-10-07-05-01-15.xls]Sheet1")&":$CX$619")
In this function you can replace this part
"'[ISS 2013-10-07-05-01-15.xls]Sheet1"
with this,
"[ISS " & TEXT(TODAY(),"yyyy-mm-dd")&"-"&TEXT(TIME(HOUR(NOW()),15,30),"hh-mm-ss").xls]Sheet1"
You can then paste the expanded construct in exactly the same place as shown above.
As previously pointed out, the 15 and 30 (minutes and seconds) are variables that you have to find a way to easily adjust.

Faustulus - thank you for the detailed explanation after having problems with the time stamp piece working getting an error at the end of the the function. The underlined portion below:

 =VLOOKUP($A19,INDIRECT(ADDRESS(2,2,1,1,"[ISS " & TEXT(TODAY(),"yyyy-mm-dd")&"-"&TEXT(TIME(HOUR(NOW()),15,30),"hh-mm-ss").xls]Sheet1")&":$CX$619"),3,FALSE).  Working to see if the the time stamp can be removed.

My only problem now is that this is requiring the source file to be open to update is there any other way to accomplish with out having to open the file to update?

Yes, there is an error. Here is the corrected version. Sorry about that.

=VLOOKUP($A19,INDIRECT(ADDRESS(2,2,1,1,"[ISS " & TEXT(TODAY(),"yyyy-mm-dd")&"-"&TEXT(TIME(HOUR(NOW()),15,30),"hh-mm-ss")&".xls]Sheet1")&":$CX$619"),3,FALSE)

Select allOpen in new window

The question we deal with here is how to make your VLOOKUP function dynamic. Let us work on that until the question is answered.

As for your other question, i.e. how to retrieve data from a closed workbook, I suggest you raise another question. That will create another thread in which we can work out a competitive solution for you.

No error inputting the function however not getting any data either getting #REF

You will get a REF error if the source isn't found. To test, extract the ADDRESS part of the VLOOKUP to a cell and check if the reference is correct:-
=ADDRESS(2,2,1,1,"[ISS " & TEXT(TODAY(),"yyyy-mm-dd")&"-"&TEXT(TIME(HOUR(NOW()),15,30),"hh-mm-ss")&".xls]Sheet1")&":$CX$619")

Hi this is what I get when I move the address out '[ISS 2013-10-14-11-15-30.xls]Sheet1'!$B$2:CX$619  the issue is the time stamp the actual file name is ISS 2013-10-14-05-05-41.  The hour is looking at the time I am trying to update the report and not when the original report was pulled.  The report will always be ran at 5 am with the minutes and second being the variable.

Thank you for all of your assistance.