# VLOOKUP and INDIRECT

I believe that an indirect function will satisfy my needs however I am not sure how to go about using.  I need to do a vlookup to a file where the name changes based on the date and time stamp.   Here is the the vlookup with the indirct =VLOOKUP(\$A17,'[ISS 2013-10-07-05-01-15.xls]Sheet1'!\$B\$2:\$CX\$619,3,FALSE).  Is it possible to use the indirct to pull the data from the new file, it is updated once a day?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
0
Commented:
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"))
``````
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"))
``````
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
``````
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.
0
Author Commented:
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?
0
Commented:
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
That would look like this (for testing),

Now, going back to the INDIRECT function,
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.
0
Author Commented:
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?
0
Commented:
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)
``````

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.
0
Author Commented:
No error inputting the function however not getting any data either getting #REF
0
Commented:
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:-
0
Author Commented:
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.
0
Commented:
You have the correct idea but the wrong tool. With a VLOOKUP you will never achieve either of these two of your actual requirements:-
1. You will not be able to look for a file whose name you only know approximately (don't know minutes and seconds)
2. You will not be able to access the file while it is closed.
I recommend to make a clean break from this failed line of thought. Close this thread. Assign points if you feel you have been served. Open a new thread where you state your wishes in a clear manner. With a new, clear focus for both sides you will be up and running in no time. Post a link to the new thread here if you wish to pull experts with you to the next level.
0

Experts Exchange Solution brought to you by