I need Excel help.....again.

  Forum Editor 10:47 04 Mar 2006

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:-


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.


  [DELETED] 10:58 04 Mar 2006

I've mailed you.

  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.

  [DELETED] 11:21 04 Mar 2006

If you copy the formula in Column H down as far as you wish (say to Row 100) the formula will not display anything if there isn't an entry in Columns C, D or F so it will not display a result until you make an entry in C, D or F on a new row.

That is a result of this part of the formula


which says if columns C, D and F are all blank on the current row, leave the cell in Column H blank. If C, D or F contain entries then the result of the second part of the formula


is displayed.

  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?

  [DELETED] 11:42 04 Mar 2006

The trick here is to display the totals at the top of the sheet. Also, to click in A3 and select Window|Freeze panes so that the column headings and the column totals are always displayed no matter how far you scroll down.

The formula for Column C is:


(The maximum number of rows in a worksheet is 2^16 or 65,536.)

  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.

  [DELETED] 14:20 04 Mar 2006

F.E. and VoG™, I've been trying to get round the self totalling puzzle for ages, and it didn't occur to me to put the total at the top of the list. Thanks to you both for the obvious solution :-))

  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

Amazon Prime Day 2018: Best Deals & Launches

The Huawei P20 Pro is the best phone for artists, designers and photographers

The best Amazon Prime Day Apple deals 2018

TV & Streaming : comment regarder la Supercoupe d’Espagne ?