I need Excel help.....again.

  Forum Editor 10:47 04 Mar 2006
Locked

Regular forum users will know that the part of my brain that should have been programmed to understand Excel formulae is missing, and I have to ask for expert help from time to time.

I have a standard project control worksheet that records the finance situation on each of my company's projects at any given moment for my private use, and it works well, save one problem.

Here's the formula that's operating at present:-

=IF(AND(C21="",D21="",F21=""),"",SUM(C$2:C21)-SUM(D$2:D21)-SUM(F$2:F21))

Columns E & G are blank - I add text to them - H is where the balance is automatically entered.

At the moment, if I add any entry below the last current row nothing happens to the total in the final column (H), I have to add rows above the last row to make it work. Is there a way I can make the total in the final column automatically move down and update as I add more data?

An additional refinement would be to have each column self-total as I added data, and for the final column total to ignore the self-totalling in the other columns.

Or am I getting ahead of myself? I can email a sample dummy file if it helps.

  johnnyrocker 10:57 04 Mar 2006

is it possible to add data below the last line and before the total one? it should work then, for the formulae to work it will add anything inserted after last line but not before it.


johnny

  Forum Editor 11:08 04 Mar 2006

You have mail.

  Forum Editor 11:09 04 Mar 2006

Thanks for responding. What I want to be able to do is enter data on a new row, and have the total from the final column in the row above automatically jump down a row, so that as I add new rows the total moves down with me.

  Forum Editor 11:29 04 Mar 2006

Many thanks - what a wonderful thing a little commonsense is.

Any thoughts on the second aspect of my enquiry - how to arrive at self-totalling on C, D, and F without the H total increasing by the amount of those totals?

  Forum Editor 11:49 04 Mar 2006

What a life-saver you are - many thanks indeed, and for the sample file you sent me. Once again the power of the forum has been wonderfully demonstrated.

Exit a happy man.

  Forum Editor 14:33 04 Mar 2006

thank our resident Excel wizard. He's helped me with Excel before, and no doubt will do so again.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor View 20 review

How Esports branding got a brand new look

Best free Mac apps

Les meilleurs VPN gratuits (2019)