look up formula

G

Guest

I am using a formula to gather info from 4 worksheets in a separate workbook
but same folder and am getting an error of formula too long. Can anyone help
with an alternative formula that will do the same thing.
My formula is as follows:
=IF(ISERROR(VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin''
Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and Settings\Owner\My
Documents\Makin'' Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg 2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg
2)'!$C$5:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)))
 
B

Biff

Hi!

Break the long formula up into 4 separate formulas then sum those 4 cells.

When the length of formulas is so long that every key stroke counts:

Place the file in a directory that doesn't have such a long path or rename
the directory to something not so long.
Don't use such long file names
Don't use such long sheet names.

Just think, the next version of Excel will allow formulas to be up to 8
times longer! How would one read that?

Biff
 
B

Bob Phillips

Why don't you put this part of the formula

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin Life
Easy\[Family Contributions 2005.xls]Girls Term 1'!$C$4:$T$40,18,0))

as a separate formula in another cell, say H10,

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Girls Term 1'!$C$4:$T$40,18,0))

as asparate formula in say I10

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Boys Term 1'!$C$4:$T$40,18,0))

as aseparate formula in say J10

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0))

as a separate formula in K10

and then you can use

=IF(ISERROR(H10,0,(H10)+IF(ISERROR(I10),0,(I10))+IF(ISERROR(J10,0,(J10)+IF(I
SERROR(K10,0,(K10)

Much more maintainable.
 
G

Guest

Hi Biff,
Between you and Bob Phillips you have given me sensible advise. Don't know
why I didn't think of it. Makes sense. Duhhh!
Thanks greatly
Sue

Biff said:
Hi!

Break the long formula up into 4 separate formulas then sum those 4 cells.

When the length of formulas is so long that every key stroke counts:

Place the file in a directory that doesn't have such a long path or rename
the directory to something not so long.
Don't use such long file names
Don't use such long sheet names.

Just think, the next version of Excel will allow formulas to be up to 8
times longer! How would one read that?

Biff

Sue said:
I am using a formula to gather info from 4 worksheets in a separate
workbook
but same folder and am getting an error of formula too long. Can anyone
help
with an alternative formula that will do the same thing.
My formula is as follows:
=IF(ISERROR(VLOOKUP(C8,'C:\Documents and Settings\Owner\My
Documents\Makin''
Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and Settings\Owner\My
Documents\Makin'' Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg
2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg
2)'!$C$5:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term
1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)))
Click to expand...
Click to expand...
 
G

Guest

Hi Bob,
Thanks for the advice. Makes alot of sense. Sometimes it helps having an
extra brain 'cause can't you see the forest for the trees.
Appreciate the help greatly
Sue


Bob Phillips said:
Why don't you put this part of the formula

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin Life
Easy\[Family Contributions 2005.xls]Girls Term 1'!$C$4:$T$40,18,0))

as a separate formula in another cell, say H10,

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Girls Term 1'!$C$4:$T$40,18,0))

as asparate formula in say I10

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Boys Term 1'!$C$4:$T$40,18,0))

as aseparate formula in say J10

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0))

as a separate formula in K10

and then you can use

=IF(ISERROR(H10,0,(H10)+IF(ISERROR(I10),0,(I10))+IF(ISERROR(J10,0,(J10)+IF(I
SERROR(K10,0,(K10)

Much more maintainable.

--
HTH

Bob Phillips

Sue said:
I am using a formula to gather info from 4 worksheets in a separate workbook
but same folder and am getting an error of formula too long. Can anyone help
with an alternative formula that will do the same thing.
My formula is as follows:
=IF(ISERROR(VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin''
Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and Settings\Owner\My
Documents\Makin'' Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg 2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg
2)'!$C$5:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)))
Click to expand...
Click to expand...
 
B

Biff

You're welcome. Thanks for the feedback!

Biff

Sue said:
Hi Biff,
Between you and Bob Phillips you have given me sensible advise. Don't
know
why I didn't think of it. Makes sense. Duhhh!
Thanks greatly
Sue

Biff said:
Hi!

Break the long formula up into 4 separate formulas then sum those 4
cells.

When the length of formulas is so long that every key stroke counts:

Place the file in a directory that doesn't have such a long path or
rename
the directory to something not so long.
Don't use such long file names
Don't use such long sheet names.

Just think, the next version of Excel will allow formulas to be up to 8
times longer! How would one read that?

Biff

Sue said:
I am using a formula to gather info from 4 worksheets in a separate
workbook
but same folder and am getting an error of formula too long. Can
anyone
help
with an alternative formula that will do the same thing.
My formula is as follows:
=IF(ISERROR(VLOOKUP(C8,'C:\Documents and Settings\Owner\My
Documents\Makin''
Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and Settings\Owner\My
Documents\Makin'' Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg
2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg
2)'!$C$5:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term
1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg
2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)))
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

Amen to that. When I started in IT, I was charged with writing reports in a
report query language similar to IB's Focus. There was a lady in the firm
who was an expert, and whenever I had a problem, I asked her advice. She
once complained to me that whenever I took a problem to her, I ended up
solving it as I explained it. But as I said to her, knowing how good and how
precise she was made me gather the facts more rigorously, and explaining it
and answering her questions gave me extra insight. She may not have solved
them, but she did assist me enormously.

--
HTH

Bob Phillips

Sue said:
Hi Bob,
Thanks for the advice. Makes alot of sense. Sometimes it helps having an
extra brain 'cause can't you see the forest for the trees.
Appreciate the help greatly
Sue


Bob Phillips said:
Why don't you put this part of the formula

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin Life
Easy\[Family Contributions 2005.xls]Girls Term 1'!$C$4:$T$40,18,0))

as a separate formula in another cell, say H10,

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Girls Term 1'!$C$4:$T$40,18,0))

as asparate formula in say I10

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Boys Term 1'!$C$4:$T$40,18,0))

as aseparate formula in say J10

VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin'' Life
Easy\[Family Contributions 2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0))

as a separate formula in K10

and then you can use

=IF(ISERROR(H10,0,(H10)+IF(ISERROR(I10),0,(I10))+IF(ISERROR(J10,0,(J10)+IF(I
SERROR(K10,0,(K10)

Much more maintainable.

--
HTH

Bob Phillips

Sue said:
I am using a formula to gather info from 4 worksheets in a separate workbook
but same folder and am getting an error of formula too long. Can
Click to expand...
anyone
help
with an alternative formula that will do the same thing.
My formula is as follows:
=IF(ISERROR(VLOOKUP(C8,'C:\Documents and Settings\Owner\My Documents\Makin''
Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and Settings\Owner\My
Documents\Makin'' Life Easy\[Family Contributions 2005.xls]Girls Term
1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg 2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Girls Term 1 (Pg
2)'!$C$5:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1'!$C$4:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents and
Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1'!$C$4:$T$40,18,0)))+IF(ISERROR(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)),0,(VLOOKUP(C8,'C:\Documents
and Settings\Owner\My Documents\Makin'' Life Easy\[Family Contributions
2005.xls]Boys Term 1 (pg 2)'!$C$5:$T$40,18,0)))
Click to expand...
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

How create a formula series with series inside 3
auto update function from workbook to another worksheet 3
formula help 3
Vlookup file location changes depending on value within cell 1
Vlookup formula 1
Vlookup not consistently grabbing the data requested 1
if and then formula 2
VLOOKUP linking to different files 2

Top