Formula limit in XL 2000 is 1,024 characters:
http://support.microsoft.com/default.aspx/kb/264626

What happens if you add the FALSE argument to the VLOOKUP?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"JR Hester" wrote:

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

Adding the FALSE argument made no difference in the results. Thanks for the
suggestion.

"Dave F" wrote:

Formula limit in XL 2000 is 1,024 characters:
http://support.microsoft.com/default.aspx/kb/264626

What happens if you add the FALSE argument to the VLOOKUP?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"JR Hester" wrote:

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

So what are you trying to do here? Your RIGHT-DAY-CHOOSE-MONTH-YEAR
combination can easily be replaced by the (more concise) =TEXT(C2,"ddmmmyyyy")

You need to use INDIRECT here, but that function doesn't work on closed
workbooks.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"JR Hester" wrote:

Adding the FALSE argument made no difference in the results. Thanks for the
suggestion.

"Dave F" wrote:

Formula limit in XL 2000 is 1,024 characters:
http://support.microsoft.com/default.aspx/kb/264626

What happens if you add the FALSE argument to the VLOOKUP?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"JR Hester" wrote:

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