How to retain formula when inserting rows?

[redryder]

Hi guys, I need some help.


Lets say I'm creating an Excel spreadsheet that will calculate the sum of cells A1:D1. The formula is stored in cell E1. I then drag the formula in E1 down to E100. This spreadsheet is to be handed over to someone else to use.

Here's my problem. If that person inserted a row in A30, the cell in E30 would now be blank. There is no formula in E30 anymore that will compute the sum of A30:D30. It is also important for E30 to display the sum in addition to calculating it.

I've seen some solutions online where the problem is solved via a different method of inserting row? Unfortunately I have no control over how a row will be inserted by the people using my spreadsheet.

I need a solution without resorting to VBA or a macro. Can anyone help?

[windknife]

Perhaps you might consider to move formula of E column to another sheet.

HTH,

windknife

[JBeaucaire]

Perhaps you might consider to move formula of E column to another sheet.

This is half the solution, because even on the second sheet the formulas would adjust themselves downward if a row was inserted on the first sheet. At least it would if the formula was a direct reference.

Instead, on sheet2, to sum A1:D1, use an indirect reference. In row 1 on sheet2 somewhere put this formula:

=SUM(INDIRECT("Sheet1!A" & ROW() & ":D" & ROW()))
...then copy that down 150 rows.

Now you will get the sum of all the active rows, no matter what happens on Sheet1 with inserting/deleting rows.

[redryder]

This is half the solution, because even on the second sheet the formulas would adjust themselves downward if a row was inserted on the first sheet. At least it would if the formula was a direct reference.

Instead, on sheet2, to sum A1:D1, use an indirect reference. In row 1 on sheet2 somewhere put this formula:

=SUM(INDIRECT("Sheet1!A" & ROW() & ":D" & ROW()))
...then copy that down 150 rows.

Now you will get the sum of all the active rows, no matter what happens on Sheet1 with inserting/deleting rows.

Thanks but that's still only half a solution right? I need the sum of A1:D1 to be displayed on E1 on the first sheet. If someone inserts a row(e.g. row 100), and enters data into A100:D100, E100 would be still be blank on Sheet 1 regardless of what happens in Sheet2.

1. How do I display the sum (A100:D100) on E100?
OR
2. How do I prevent someone else from inserting rows, but still allowing them to enter data in the cells?

[JBeaucaire]

Oh, why didn't you ask that originally. Simple...protect your sheet. When you protect your sheet you get to pick which cells are locked and which are not, and decide things like allowing insertions/deletions.

[redryder]

Oh, why didn't you ask that originally. Simple...protect your sheet. When you protect your sheet you get to pick which cells are locked and which are not, and decide things like allowing insertions/deletions.

I think my Excel 2000 does not have those options. All I see are checkboxes for "Contents", "Objects" and "Scenarios" under the Protect Sheet dialog box. Nothing about inserts/deletions.

Ok, what if I use a VBA script that disables all the insert commands? I found some code online that would do it for the main menu bar, but I can't find the code to disable Insert in the right-click menu. Any help there?

[mubashir aziz]

IMO: you should upgraded to at least 2003 as before a faced a lot lot of problems while using 2000.

[JBeaucaire]

RedRyder, be careful about using the "QUOTE" button, only use that if you're specifically noting something said by someone in an earlier post, and even then probably best to cut out the rest. Just use the QUICK-REPLY to converse normally and keep the thread from filling up with unnecessary quoting.

If you have a new Q go ahead and post that in the PROGRAMMING forum as a separate question. Make sure you point out this is an Excel 2000 specific need.