Thanks for that suggestion. That does indeed redunce the length of the overal
formula.

Unfortunately I still get the same Value# error. This seems to be resulting
from inserting the filename generation routine inside the Vlookup() function.

"Dana DeLouis" wrote:

RIGHT(0&DAY(C2),2)&CHOOSE(MONTH(C2),
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","S ep","Oct","Nov","Dec")&YEAR(C2)

Hi. If I am not mistaken, the Date part of the code above may be written
like this...

=TEXT(C2,"ddmmmyyyy")

--
HTH :)
Dana DeLouis
Windows XP & Office 2007


"JR Hester" wrote in message
...
What is maximum character length of a formula in Excel 2000?

I have the following formula, which properly returns a file name and
range:
="'C:\Documents and Settings\jhester\Desktop\Testing for
clarissa\[sample"&RIGHT(0&DAY(C2),2)&CHOOSE(MONTH(C2),
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","S ep","Oct","Nov","Dec")&YEAR(C2)&".xls]Sheet1!$A$3:$E$14"

however, when I place this inside a Vlookup() function it returns that
awful
VALUE# response.The character count is approximately 220, including spaces
and punctuation. Have I exceeded a length limit or am I missing soemthing
else?
Here is the formula that fails
=Vlookup(A3, "'C:\Documents and Settings\jhester\Desktop\Testing for
clarissa\[sample"&RIGHT(0&DAY(C2),2)&CHOOSE(MONTH(C2),
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","S ep","Oct","Nov","Dec")&YEAR(C2)&".xls]Sheet1!$A$3:$E$14",
4)
Please note in the second example, that I simply added is the
"Vlookup(A3,"
at the beginning and the" , 4)" at the end

Thanks for your assistance