Adding on to a formula across rows and columns

[JeffJ]

I need to add a second section of a formula onto an existing formul
across rows and columns.

The problems are:

-Not all cells currently contain formulas (some are blank)
-Not all the formulas are the same (there are at least 2 different
formulas among all the existing formulas that I want to add that secon
formula or section to)...and the different formulas are somewha
interspersed.
-Some of the existing formulas contain relative references (I want t
change them to absolute references)
-If I use relative references to somehow copy a portion of a formul
across all remaining rows and columns, I still want to go back an
change them to absolute references (in one shot, if possible).

Right now, I'm having to go cell-by-cell and make the changes (taggin
on a second part), and there are several hundred cells.

Any ideas would be appreciated.

(e-mail address removed)
http://lightningfingers.tripod.co

 

[Doug Kanter]

How about search/replace, wherever possible?

 

[Frank Kabel]

Hi
it would be helpful if you post your existing formula and the desired
'new' formula. Otherwise difficult to say (you may use Search+Replace)

 

[David McRitchie]

Hi Jeff,
Is it a state secret to ask what you want to do the
formula and where they are.

You only want formulas changed, correct?
Any other testing.

Basically you would probably check if it is a formula,
and if it is at worst probably enclose the the existing
formula without the equal sign within parentheses
and tack on what it is that you want to append.

But without details of where and what to change I don't
see that we should have to guess at what you want..

Normally you would preselect what you want to change
and run a macro.

If it is strictly a matter of adding or multiplying by a constant
that can be done without a macro.

How about search/replace, wherever possible?

 

[JeffJ]

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="1
inch"),VLOOKUP(J6,lookup!$C$3:$D$19,2,0),IF(AND(J5=3,J2="...3/12...",J3="1
inch"),VLOOKUP(J6,lookup!$E$3:$F$19,2,0),""))

I then Paste an additional formula onto the end of it, so that i
becomes:

=IF(AND(OR(J5=1,J5=2),J2="...3/12...",J3="1
inch"),VLOOKUP(J6,lookup!$C$3:$D$19,2,0),IF(AND(J5=3,J2="...3/12...",J3="1
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 t
$C$34.

Also, the original formula contains relative references. I want t
change them to absolute references. So I click to the right of eac
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="1
inch"),VLOOKUP($J$6,lookup!$C$3:$D$19,2,0),IF(AND($J$5=3,$J$2="...3/12...",$J$3="1
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 differen
(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="1
inch"),COUNTIF($J$7:$J$16,"=E-120-D"))

After I Paste in the end portion and change the row reference, i
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="1
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, represen
the only 2 types of original formulas I have (before I tag on the extr
"+ IF" arguments).


(e-mail address removed)
http://lightningfingers.tripod.co

 

[JeffJ]

Thank you, David and Frank, for your replies.

See my reply to Doug.

I was trying to avoid posting all those complex formulas and taking u
all that space; that's why I originally tried to generalize it in word
alone

 

[David McRitchie]

Hi Jeff,
So what do blank cells have to do with the formula.
You want to learn how to write your formulas so that you can use
the fill handle. You want to use $C33 in your formula
so that it will copy down. you want to use formulas that
reference from the current row so that if you insert/delete
rows the formula won't have to change. I don't really
have an idea of what is changing from one row to another
other than $C33 (instead of $C$33). When you use
$C33 that is in row 33 correct. If your are referring to
row 33 from ad different row you probably want to use offset.

If you aren't using the formula if different columns, possibly teh
$C doesn't have to be absolute. either.

Use of fill-handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm
use of OFFSET
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#offset

Wouldn't it be simpler to add two more columns one with the
added parts and one to sum. In any case you can test
with a formula in a new column and fille that down to make
sure you have the same result then add the new material to it.

Absolute cell references (unless enclosed in quotes) will change
if you insert / delete rows / columns but they will not change when
you copy or use the fill handle.


welcome back Frank.

 

[JeffJ]

Thank you for your reply and advice, David.

I have some blank cells in-between the formula cells,
so I don't want to do a simple copy-down, or else it
will put the second portion of my formula into those
cells as well. Those cells will eventually get a formula,
but it will possibly be a different formula.
can use the fill handle.

The problem is, I'm doing this piece-meal. The person I'm
doing it for asked me if I could create this, and he originally
was worried that it would be impossible. He does Purchasing
and Inventory. There is a lot of info and variation going
into this. He is completely computer-illiterate, and since
I am almost clueless regarding his field, it is difficult for
us to communicate. But I suggested we tackle this a little
at a time, and so he is giving me a little information at a
time. That's why I'm having to go back and add to formulas.
He tells me things like, "OK, now I also want it to do this..."
I only started learning formulas 8-9 months ago, and even
though I'm a computer graphics person (and not a math person),
and the only other 'programming' of any sort that I've ever
done is HTML, I find doing Excel formulas to be challenging
and interesting. But I'm getting off track...

New rows will, at times, have to be inserted or deleted. He
has already asked me to delete some of them. That's why I want
to use absolute references instead of relative, because it
already messed up the formula when I previously used relative
references.
added parts and one to sum.

No, I don't think that will work in my case.

Basically, columns E:K, starting at row 31, are for rafter
amounts. Each column represents a different house.

Column C, starting at row 31, lists the various rafters,
with corresponding rows showing amounts used.

Rows 1:6 are "Production Number" (manual entry); "Pitch"
(choose size from a drop-down list); "Eaves" (drop-down list);
"Zone" (drop-down list); and "Size of House."

The various combinations that are chosen for the Pitch, Eaves,
Zone and Size determine the standard amounts of rafters (the
amounts appear automatically in columns E:K, starting at row 31,
after you make your choices from the drop-down menus).

In addition to that, rows 7:15 are Options (from drop-down lists),
which further add rafter amounts in E:K, on top of whatever
standard amounts are already showing.

In addition to that, rows 16:29 are Custom Rafters (drop-down lists)
and corresponding cells where you can manually enter any amount.
These amounts are automatically added into columns E:K (starting
at row 31) in addition to the Standard amounts and Option amounts.

Column L:O is "Total Used," "In Stock As of(date),"
"Order Amount," and "In Stock After Order." This adds and subtracts
the total amounts for each rafter.

Sorry to be so verbose, but I hope that gives a clearer picture.


(e-mail address removed)
http://lightningfingers.tripod.com

 

[David McRitchie]

Hi Jeff,
If you use OFFSET you will most likely not mess up your formulas,
so please read that, among the links I supplied previously. .

If you have a blank cell you can test that condition, and have
it *look* empty with an empty string, or use the value from the
cell above if that makes it work. Something like, I'm not looking
at your formulas this is just an example.

H33: =IF(ISBLANK(A33),"", oldformula)
or
H33: =IF(ISBLANK(A33),OFFSET(H33,-1,0),oldformula)

Since someone is likely to type in a space which will look like the
cell is empty you might as well test as follows instead.
H33: =IF(TRIM(A33)="","",oldformula)

 

[JeffJ]

David,

Thank you for those links and for your suggestions.

I checked out the links you provided and have been reading up on those
So far I haven't found an actual solution, but I have been learnin
some cool new things, and I thank you for providing that (for ex.,
had never tried using Ctrl or Shift in combination with the fill handl
before).

Upon closer examination of my formulas, the blank cells are not m
biggest problem; I have many variations of formulas, and that's wha
makes it so tough. If they were all the same formula, Edit>Fill Dow
or dragging the fill handle would be a breeze.

Doug and Frank had previously mentioned Edit>Find>Replace. That woul
be helpful, except that apparently there is a character limit in th
Find and Replace fields, for it will not let me paste my longe
formulas into Find or Replace. It might not work anyway, though, sinc
almost all my formulas vary at least slightly.

I also tried Edit>Go To...>Special>Formulas. The problem is, it find
*all* the formulas. If it would further break it down into th
different types of formulas, or similar formulas, that might work.

In any case, I will continue researching those links you provided (an
any links on those pages that lead to other sites). Even if I don'
find a perfect solution, you guys are helping me to learn more, and I'
grateful for that.

(e-mail address removed)
http://lightningfingers.tripod.co

 

[JeffJ]

David,

I can certainly see where OFFSET can prevent problems against futur
insertions of rows or columns, and I will definitely remember that.
wish I had known that when I started creating the form.

To apply OFFSET to what I already have, however, I would have to g
back and change all my various formulas to include the OFFSET function
And then I would be faced with the same problem I am faced with now:
How do I add an additional function to a group of formulas in differen
cells, when those formulas are all different?

In any case, I'm hoping that using absolute references will give me th
same basic result as OFFSET.

See, that's another problem: In order to do Copy>Paste; Edit>Fil
Down; dragging the fill handles; etc. I'm thinking I would have to us
relative references for at least part of my formulas, so that it woul
change the applicable references to reflect the cell that the formul
resides in. And, in doing that, I would still have to go bac
afterwards and change all those relative references to absolut
references (again, to protect the formulas from changing if rows ar
inserted). And I don't know any way of selecting the entire Sheet an
changing all relative references (even if a formula contains som
relative and some absolute references) to absolute references, all i
one shot. If I could do that, that would at least help in som
respect.

Sorry, I don't mean to drag this on and on. I will continu
researching the information you have given me.

(e-mail address removed)
http://lightningfingers.tripod.co

 

[JeffJ]

David,

Earlier you said:
if you insert / delete rows / columns but they will not change when you
copy or use the fill handle.

In my last post I said:the same basic result as OFFSET.

I was incorrect. As you said, when it comes to inserting rows, it seems
that whether I have absolute or relative references it doesn't make any
difference: in either case they will change.

(e-mail address removed)
http://lightningfingers.tripod.com

 

[JeffJ]

David,

After reading more of your web pages I see that holding the Alt ke
while dragging the fill handle *will* allow me to append the secon
portion of my formula, but *only* if the two (or more) origina
formulas are the same. This is at least a little helpful
unfortunately, most of my formulas are not the same. But I'm learnin
more and more.

I didn't realize you guys were such established experts. Please hav
patience with me if I seem slow to get it.

(e-mail address removed)
http://lightningfingers.tripod.co

 

[David McRitchie]

Hi Jeff,
I think you might also want to look at my formula page, so that you
can use something like
=personal.xls!getformula(h33)
to see the formula you are actually using on the same page as it's
used rather than having to switch to and from the formula view
(toggle Ctrl+` -- accent grave or whatever is to left of number row)
Tools, Options, View (tab), Formulas -- check/uncheck formula view

The advantage of the function to view a formula is that you can adjust
the column width and text size to suit your needs. Changing the wide in formula
view will also affect the wide of such columns in the normal view.

To modify your formulas you might get some ideas from
http://www.mvps.org/dmcritchie/excel/join.htm#insertprefix
http://www.mvps.org/dmcritchie/excel/join.htm#makeindirect

 

[JeffJ]

Ok, Cool! I'll have to check that out! Thanks again, David!

(e-mail address removed)
http://lightningfingers.tripod.co