Re: Adding on to a formula across rows and columns
From: JeffJ (JeffJ.165qwq_at_excelforum-nospam.com)
Date: 05/12/04
- Next message: Domenic: "Re: Conditional Formatting"
- Previous message: Frank Kabel: "Re: Linking cells"
- In reply to: Doug Kanter: "Re: Adding on to a formula across rows and columns"
- Next in thread: JeffJ: "Re: Adding on to a formula across rows and columns"
- Reply: JeffJ: "Re: Adding on to a formula across rows and columns"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 12 May 2004 14:23:16 -0500
Thank you for your suggestion/reply, Doug.
However, I don't know what I would search for.
I just need to go down the rows and, ignoring blank cells,
I need to tag on an additional formula to an existing formula.
For example, I find a cell which has the following formula:
=IF(AND(OR(J5=1,J5=2),J2="...3/12...",J3="12
inch"),VLOOKUP(J6,lookup!$C$3:$D$19,2,0),IF(AND(J5=3,J2="...3/12...",J3="12
inch"),VLOOKUP(J6,lookup!$E$3:$F$19,2,0),""))
I then Paste an additional formula onto the end of it, so that it
becomes:
=IF(AND(OR(J5=1,J5=2),J2="...3/12...",J3="12
inch"),VLOOKUP(J6,lookup!$C$3:$D$19,2,0),IF(AND(J5=3,J2="...3/12...",J3="12
inch"),VLOOKUP(J6,lookup!$E$3:$F$19,2,0),"")) + IF($J$16=$C$33,$J$17,0)
+ IF($J$18=$C$33,$J$19,0) + IF($J$20=$C$33,$J$21,0) +
IF($J$22=$C$33,$J$23,0) + IF($J$24=$C$33,$J$25,0) +
IF($J$26=$C$33,$J$27,0) + IF($J$28=$C$33,$J$29,0)
Now, since I'm on row 34, I have to change every instance of $C$33 to
$C$34.
Also, the original formula contains relative references. I want to
change them to absolute references. So I click to the right of each
reference and hit F4. It then becomes the final formula I want:
=IF(AND(OR($J$5=1,$J$5=2),$J$2="...3/12...",$J$3="12
inch"),VLOOKUP($J$6,lookup!$C$3:$D$19,2,0),IF(AND($J$5=3,$J$2="...3/12...",$J$3="12
inch"),VLOOKUP($J$6,lookup!$E$3:$F$19,2,0),"")) +
IF($J$16=$C$34,$J$17,0) + IF($J$18=$C$34,$J$19,0) +
IF($J$20=$C$34,$J$21,0) + IF($J$22=$C$34,$J$23,0) +
IF($J$24=$C$34,$J$25,0) + IF($J$26=$C$34,$J$27,0) +
IF($J$28=$C$34,$J$29,0)
But then, 2 rows down (row 36), the original formula is different
(i.e., no VLOOKUP in this one, and no COUNTIF in the other one):
=IF(AND(OR($J$6="38X12",$J$6="40X12",$J$6="42X12",$J$6="44X12",$J$6="46X12",$J$6="48X12",$J$6="50X12",$J$6="52X12",$J$6="54X12",$J$6="56X12",$J$6="58X12",$J$6="60X12",$J$6="62X12",$J$6="64X12",$J$6="66X12",$J$6="68X12",$J$6="70X12"),$J$2="...3/12...",$J$3="12
inch"),COUNTIF($J$7:$J$16,"=E-120-D"))
After I Paste in the end portion and change the row reference, it
becomes:
=IF(AND(OR($J$6="38X12",$J$6="40X12",$J$6="42X12",$J$6="44X12",$J$6="46X12",$J$6="48X12",$J$6="50X12",$J$6="52X12",$J$6="54X12",$J$6="56X12",$J$6="58X12",$J$6="60X12",$J$6="62X12",$J$6="64X12",$J$6="66X12",$J$6="68X12",$J$6="70X12"),$J$2="...3/12...",$J$3="12
inch"),COUNTIF($J$7:$J$16,"=E-120-D")) + IF($J$16=$C$36,$J$17,0) +
IF($J$18=$C$36,$J$19,0) + IF($J$20=$C$36,$J$21,0) +
IF($J$22=$C$36,$J$23,0) + IF($J$24=$C$36,$J$25,0) +
IF($J$26=$C$36,$J$27,0) + IF($J$28=$C$36,$J$29,0)
I have 2 more columns left, of 176 rows each, to do this to.
I believe the 2 different original formulas I've shown here, represent
the only 2 types of original formulas I have (before I tag on the extra
"+ IF" arguments).
jojh@aol.com
http://lightningfingers.tripod.com
--- Message posted from http://www.ExcelForum.com/
- Next message: Domenic: "Re: Conditional Formatting"
- Previous message: Frank Kabel: "Re: Linking cells"
- In reply to: Doug Kanter: "Re: Adding on to a formula across rows and columns"
- Next in thread: JeffJ: "Re: Adding on to a formula across rows and columns"
- Reply: JeffJ: "Re: Adding on to a formula across rows and columns"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
