Vlookup with Variable Dates AGAIN

[Smeggy]

Hi All,
From a previous post and reply i'm trying to use a vlookup with variable dates, i'm struggling again, additionally
with this one though the sheet i'm searching in need column C to be converted to numbers and i would prefer not to have to
do that if possible.

Is there any way to complete the search without a convert to number first?
Then why doesn't the second formula work that i'm trying.

This is not being entered into the cell via VB just simply pasted.


Any ideas?


=VLOOKUP(A5,'[MDS 2014-09-16.xlsb]MDS'!$C:$T,18,FALSE)
=VLOOKUP(A5,'[MDS" & format(now, "yyyy-mm-dd") & ".xlsb]MDS'!$C:$T,18,FALSE)

 

[Andrew Poulsom]

What's in A5 on the sheet containing the formula and column C on sheet MDS?

For the second formula you need INDIRECT:

=VLOOKUP(A5,INDIRECT("'[MDS"&TEXT(NOW(),"yyyy-mm-dd")&".xlsb]MDS'!$C:$T"),18,FALSE)

 

[Smeggy]

Hi Andrew,
Apologies, there is nothing wrong with the first formula apart from it being absolute, i.e. the date is not variable and
would need to be re-written every time the my sheet is created. I needed to create a formula that would look at today's MDS.
Essentially this is a file that is re-produced and sent out on a daily basis.
The problem lies not with the sheet where teh vlokup is being placed but rather the MDS. The columns that im using to do the
Vlookup is in my file numbers and in the MDS requires converting to numbers.
if i try to break it down:

A5 - is fine and is a number
MDS(Book) is a constant
Date (now, " yyyy-mm-dd") is variable
MDS(Sheet) is constant
C = a number requiring converting to number

I hope that makes a little more sense.

Regards

 

[Andrew Poulsom]

In what way doesn't the formula I gave work for you?

 

[Smeggy]

Andrew,
For whatever reason it wouldn't read column C, it produced an #N/A, i added ""& which seems to work, the
formula now looks like:
=VLOOKUP(""&A6,INDIRECT("'[MDS"&TEXT(NOW()," yyyy-mm-dd")&".xlsb]MDS'!$C:$T"),18,FALSE)

 

[Andrew Poulsom]

That's because you were trying to look up a number in a column of text. What you added coerces the number to text.

 

[Smeggy]

Andrew,
Thank you for the help... like always, very appreciated.