Embedding formula in a formula
 

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

Embedding formula in a formula
 

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

Embedding formula in a formula
 

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

Embedding formula in a formula
 

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

Embedding formula in a formula
 

I don't believe what you're trying to do will fly. The second argument of
VLOOKUP requires a _range reference_. You are providing a string that looks
like a range reference. It's much the same thing as entering ="A1" in a
cell and expecting it to evaluate like =A1. Excel does provide the INDIRECT
function which turns a string into a range reference, but that does not work
with closed workbooks. If the target workbook will be open then I think you
can do what you want. In that event the reference string would include the
file's name without a path.

--
Jim
"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
|
|

Embedding formula in a formula
 

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

Embedding formula in a formula
 

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