Using an external reference to a filename as a date

[b0yb00ger]

Hi, I am just new to this forum and am in great need of help

I am trying to create an external reference to an excel file called...
'OS orders 15-06-04.xls'. The formula I am wanting to use (shortene
for simplicity) is...

=VLOOKUP($a$1,[OS orders 15-06-04.xls]OS Sales orders'!$I:$S,4,false)

I am trying to create a macro that will write this formula, copy th
formula over a wide range of blank cells, then copy and paste th
values for presentation with other information.

My problem is... the date in the external references filename change
everyday 'OS orders *15-06-04*.xls'. My question is... Is it possibl
to update the filename by using a function such as NOW() or TODAY()?

I can create the filename easily in a seperate cell as text with
formula such as...

="'OS orders "&TEXT(Today(), "dd-mm-yy")&".xls'!$I:$S"

But I am burning my brain out on how to blend it in with my vlooku
formula!! Surely this problem has happened and hopefully been solve
before but I cannot find any aid on the internet anywhere.

Please help! All solutions and ideas will be greatly appreciated

 

[Frank Kabel]

Hi
in most cases INDIRECT would work. But this requires that the
referenced file is opened. If your file is NOT open there are several
other solutions but all of them won't work in your case as you need the
function to return a range (second parameter of VLOOKUP). This is not
possible with only worksheet functions in your specific case (but maybe
Harlan will prove me wrong on this).

Just for you as a reference: See the following link for some workaround
solutions to access closed files: http://tinyurl.com/2c62u

 

[b0yb00ger]

I will be using VBA to write the formula, so if it is possible to us
VBA to manipulate the formula in some way would be more than helpful.

My brain is on overload because after (or if) you guys can get this t
work I will need to add in many IF and ISERROR statements. Because i
the TODAY() filename doesn't exist I want my formula to try TODAY()-
etc.

 

[b0yb00ger]

Me again!

I have came up with this simple formula below... Unfortunately i
doesn't work... I am hoping my formula below may help someone t
visualise what I am trying to do and maybe that somebody has the exce
knowledge to come up with a solution for the problem.

Cell (A1)= VLOOKUP("DueItem1",[OS orders
Cell (A2)= =TEXT(TODAY(), "dd-mm-yy")
Cell (A3)= .xls]OS Sales orders'!$I:$T,6,FALSE)
Cell (A4)= =A1&A2&A3

---> Cell A4 will result: VLOOKUP("DueItem1",[OS orders 16-06-04.xls]O
Sales orders'!$I:$T,6,FALSE)


Cell (A5)= =INDIRECT(A4,TRUE)

---> Cell A5 will result in a REF# error but the actual formula...

=VLOOKUP("DueItem1",[OS orders 16-06-04.xls]OS Sale
orders'!$I:$T,6,FALSE)

works fine!

Please note for the INDIRECT worksheet function to work... the externa
reference spreadsheet needs to be open. Still however my formul
refuses to work... Any idea's or solutions are truly welcome