Saving Formulas when addin rows

[nova]

I have a ss that I need to keep track of 12 weeks on information. Each week
I need to add a new row either by moving the remaining rows down or deleting
th lar row and insert another row. Either way I do it I lose my formulas for
the new row.
Example
Row 2 has formulas in columns F, G, H
I now move the data in row 2 down, the formulas in F, G, H are gone as well.
On the bottom of the SS I have formulas to add certain rows together.
Before I move row 2 all columns have correct formulas. I add the new row
which no becomes Row, the formula is no longer in row 2 and I have to edit
every row to include row 2.

Sorry to be so wordy but I need help!

Thanks

 

[JLatham]

I don't think you mean that the formulas in previous rows are actually gone,
as in not there at all; but you mean that they've changed and thus lost the
reference to some rows you've moved?

This could probably be solved in large part with the use of named ranges for
the data in various columns. But we won't go into that here.

One possible help is to use an absolute reference to row 2 in your formulas.

Current formula example: =SUM(A2:A99)
Revised formula: =SUM(A$2:A99)
as you insert rows ahead of row 99, the formula will update correctly. But
rows added after row 99 would require a change to the ending address.

One way to keep a total for a column adding properly, assuming the SUM() is
in that column, one row below the last entry to be added might be this kind
of setup:

In the cell at the bottom of the column (assumed to be A here and assumed to
be in row 19 - so this formula would go into A19) put a formula like this:
=SUM(INDIRECT("A$2:A" & ROW()-1))
that'll make an address reference that always goes down column A from row 2
to the row just above where you have the =SUM(INDIRECT(...)) formula.

Hope this helps some.

 

[nova]

Thanks, but I do not think I am explaining myself very well. I hav 2 SS one
does what I need it to but the other one does not. I know what I want but
can't explain it in a forum. One sugestion was to us format painter but that
did not work either.

Thanks for your help

 

[JLatham]

Can you send sample file(s) as email attachments to
HelpFrom @ jlatham site.com
(remove the spaces from the email address of course)
and explain or point out in them what is not correct? Perhaps a 'before'
and an 'after' example file?

 

[nova]

Thanks..I sent it to you

Can you send sample file(s) as email attachments to
HelpFrom @ jlatham site.com
(remove the spaces from the email address of course)
and explain or point out in them what is not correct? Perhaps a 'before'
and an 'after' example file?

 

[JLatham]

I have received the email with attachments. I'll examine them tomorrow (ok,
later today - after the sun comes back up). If there's something that will
add to the knowledge pool that comes out of this, I'll post that information
as a follow up here.

 

[JLatham]

Now that I see the file, I understand the problem. Let me see if I can
explain what's happening before giving you the fix (yes there is one).

Lets use the formula at B13 in the Before book as an example, the
explanation applies to all formulas on that row.

The formula is = SUM(B2:B12)

You go up to row 2 and insert a new row. This forces row 2 (B2) to become
B3 and Excel automatically adjusts the formula at B13 (now B14). This leaves
the new row 2 out in the cold. If you had inserted the new row at row 3,
then the formula in B13 (now B14) would have changed to become =SUM(B2:B13) -
taking into account the new row that was added WITHIN the original range.

The same kind of situation would take place if, in the original Before file,
you'd selected row 13 and inserted a new row there. But if you'd chosen row
12 and inserted a new one, then the formula would have adapted properly.

The (almost there) solution is simple: Set the formulas up to reference row
2 "absolutely". They are now referenced "relatively", meaning they
column/row indicators can change as things are moved around. What you want
to do is pin row 2 down firmly, while letting the end of the range remain
able to change.

If you change the formula at B13 to be
=SUM(B$2:B12), then as you add new entries by inserting new rows at row 2,
it's all going to work well for you. ... For that first group.
Unfortunately, adding the row in the top group forces all groups below it
down one row also, which would throw off their formulas if they also were set
up the same way.

You've got a couple of choices here:
#1 put each person's record on a separate sheet.

#2 (and I'll bet this is the one you decide to use). Change the SUM()
formulas in each of the "Total for..." rows to include the row with the
person's name in it. So =SUM(B2:B12) will become =SUM(B1:B12). Same for the
others on row 13.

For the second group in the Before file, at B30, change the formula from
=SUM(B18:B29) to =SUM(B17:B29). Same for the rest of the groups on the sheet.

The text in the first row of the range (rows 1 and 17 in the revised
formulas I worked with here) will not affect the value of the total, it will
be treated as 0.

I hope this helps your tool become friendlier and more useful for you.

 

[nova]

Thank you so much! I am going to work on this now. You have been a great
help!

 

[JLatham]

Glad I've given you some things to work with. Hope it all works out well for
you.

Thanks for the feedback.
JLatham