| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
look up formula
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:\Do cuments 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:\D ocuments 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:\Do cuments 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))) |
|
#2
|
|||
|
|||
|
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" wrote in message ... 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:\Do cuments 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:\D ocuments 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:\Do cuments 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))) |
|
#3
|
|||
|
|||
|
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))+I F(ISERROR(J10,0,(J10)+IF(I SERROR(K10,0,(K10) Much more maintainable. -- HTH Bob Phillips "Sue" wrote in message ... 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:\Do cuments 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:\D ocuments 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:\Do cuments 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))) |
|
#4
|
|||
|
|||
|
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" wrote: 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" wrote in message ... 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:\Do cuments 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:\D ocuments 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:\Do cuments 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))) |
|
#5
|
|||
|
|||
|
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" wrote: 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))+I F(ISERROR(J10,0,(J10)+IF(I SERROR(K10,0,(K10) Much more maintainable. -- HTH Bob Phillips "Sue" wrote in message ... 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:\Do cuments 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:\D ocuments 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:\Do cuments 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))) |
|
#6
|
|||
|
|||
|
You're welcome. Thanks for the feedback!
Biff "Sue" wrote in message ... 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" wrote: 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" wrote in message ... 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:\Do cuments 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:\D ocuments 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:\Do cuments 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))) |
|
#7
|
|||
|
|||
|
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" wrote in message ... 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" wrote: 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))+I F(ISERROR(J10,0,(J10)+IF(I SERROR(K10,0,(K10) Much more maintainable. -- HTH Bob Phillips "Sue" wrote in message ... 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:\Do cuments 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:\D ocuments 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:\Do cuments 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))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula? | Excel Worksheet Functions | |||
| writing a formula for a colored value | New Users to Excel | |||
| referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
| Simplify formula | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
