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?
jmac001Asked:
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.

barry houdiniCommented:
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
FaustulusCommented:
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"))

Open 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"))

Open 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

Open 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.
0
jmac001Author 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
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

FaustulusCommented:
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.
0
jmac001Author 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
FaustulusCommented:
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)

Open 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.
0
jmac001Author Commented:
No error inputting the function however not getting any data either getting #REF
0
FaustulusCommented:
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")
0
jmac001Author 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
FaustulusCommented:
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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmac001Author Commented:
Thank you for all of your assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.