Haveing problems inserting a row

[zerocool]

Here is the problems
I am working off of 3 different workbooks that are linked together
Every time that I insert a new row, the forumlas do not update. I
there a way for me to insert a new row so that it will change th
forumlas?? I am hopping this could be done?

thanks for any help you can give me
Turb

 

[John Thow]

Here is the problems
I am working off of 3 different workbooks that are linked together.
Every time that I insert a new row, the forumlas do not update. Is
there a way for me to insert a new row so that it will change the
forumlas?? I am hopping this could be done?

thanks for any help you can give me
Turbo

Hi,

If your sheet looks like:-

A1 1
A2 2
A3 3
A4 =Sum(A1:A3)

and you add a row immediately before the row before the formula,
the formula won't update. If you do it this way:-

A1 1
A2 2
A3 3
A4
A5 =Sum(A1:A4)

and add the new row before the empty one, it will update the formula.

HTH
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[John Thow]

Hi,

If your sheet looks like:-

A1 1
A2 2
A3 3
A4 =Sum(A1:A3)

and you add a row immediately before the row before the formula,

Sorry, that's supposed to be "and you add a row immediately before the
formula"

the formula won't update. If you do it this way:-

A1 1
A2 2
A3 3
A4
A5 =Sum(A1:A4)

and add the new row before the empty one, it will update the formula.

HTH
--

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Dave Peterson]

If I understood your question correctly, I think you'll have to have the other
workbooks open so they can react to your change.

Another option--depends on what you're doing is to use some defined names.

then instead of stuff like this that refers to the address:
=SUM('C:\My Documents\excel\[book1.xls]Sheet1'!$A$1:$A$14)

You could refer to the range name:
=SUM('C:\My Documents\excel\book1.xls'!QtyReceivers)

Then when that range named QtyReceivers gets resized, the other workbook will
still be ok.

 

[zerocool]

thanks for the help but that is not full compluting my problems. i hav
3 WB that are format the same. One is for employee tips year to dats
one for how much they are getting tiped (%), abnd the other whixh bot
books are locked. the third book is for superviser to add in the amou
each employee is getting tiped and who get exter money for doing
really good job.

each employee is in differnet groups (server, caption,..........) I wa
just give the ok to hire more people. the WB groups is some areas ar
full and I need to add more people. Insted of re-writing each formul
is there a way to add a line and not have to change all the formulas??
it would not be a problem to re-wright the formulas is i did not hav
over 400 different cells to change.

hope that help in understanding my question
turb

 

[John Thow]

thanks for the help but that is not full compluting my problems. i have
3 WB that are format the same. One is for employee tips year to dats,
one for how much they are getting tiped (%), abnd the other whixh both
books are locked. the third book is for superviser to add in the amout
each employee is getting tiped and who get exter money for doing a
really good job.

each employee is in differnet groups (server, caption,..........) I was
just give the ok to hire more people. the WB groups is some areas are
full and I need to add more people. Insted of re-writing each formula
is there a way to add a line and not have to change all the formulas??
it would not be a problem to re-wright the formulas is i did not have
over 400 different cells to change.

hope that help in understanding my question
turbo

Sorry, I think you're going to have to amend the formulas....

It might not be quite as bad as you think. If you have a row with (say)
totals of the above n rows, insert a new row above the totals row & amend the
range of rows totalled for that column. Now copy that cell and paste it to
the rest of the total formula cells on that row. As long as you have used
relative references (eg A1:A10) in the changed formula, the paste will
automatically insert the correct reference for the column it's in. [ If you
use absolute references (eg $A$1:$A$10) it _won't_ work; every total will
show the results for column A.]

Remember, in case you need to add new rows in the future, always leave an
empty row before the formula row but include that row in the formula.
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[David McRitchie]

Hi zerocool,
In an earlier reply of John Thow's in this thread, he suggested inserting a blank row
and provided the following example to avoid rewriting the formula after
insertion/deletion of a row immediately above the formula.

A1 1
A2 2
A3 3
A4
A5 =Sum(A1:A4)

Instead of inserting a useless empty row on your sheet, use the following:
A1: 1
A2: 2
A3: 3
A4: =Sum(A1:OFFSET(A4,-1,0)

You might also look at my page
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm