Saving Formulas when addin rows
Thanks..I sent it to you
"JLatham" wrote:
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" wrote:
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" wrote:
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" wrote:
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
|