Embedding formula in a formula

G

Guest

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","Sep","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","Sep","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
 
D

Dana DeLouis

RIGHT(0&DAY(C2),2)&CHOOSE(MONTH(C2),
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","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")
 
G

Guest

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

Dave F said:
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 said:
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","Sep","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","Sep","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
Click to expand...
Click to expand...
 
J

Jim Rech

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
| 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","Sep","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","Sep","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
|
|
 
G

Guest

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 said:
Adding the FALSE argument made no difference in the results. Thanks for the
suggestion.

Dave F said:
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 said:
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","Sep","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","Sep","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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
RIGHT(0&DAY(C2),2)&CHOOSE(MONTH(C2),
Click to expand...
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","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 said:
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","Sep","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","Sep","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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find the position of a date 6
YTD budget based on current input of actual 4
weeks in a month 1
Still trying to average 4 worksheets and having formula problems 3
IF formula - odd/even months and different names 2
Formula Help for Cumulative Result 5
Formula IF 1
Month question? 10

Top