I need Excel help.....again.

  Forum Editor 10:47 04 Mar 06
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 06

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

  VoG II 10:58 04 Mar 06

I've mailed you.

  Forum Editor 11:08 04 Mar 06

You have mail.

  Forum Editor 11:09 04 Mar 06

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.

  VoG II 11:21 04 Mar 06

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

IF(AND(C21="",D21="",F21=""),""

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

SUM(C$2:C21)-SUM(D$2:D21)-SUM(F$2:F21)

is displayed.

  Forum Editor 11:29 04 Mar 06

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?

  VoG II 11:42 04 Mar 06

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:

=SUM(C3:C65536)

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

  Forum Editor 11:49 04 Mar 06

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.

  Gongoozler 14:20 04 Mar 06

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 06

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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…