We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

Contact Forum Editor

Send an email to our Forum Editor:

PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

# Tech Helproom

It's free to register, to post a question or to start / join a discussion

# Excel formula for brain gone on holiday

Belfix

Likes # 0

Hi..very basic but I think my brain has done a runner. If I have a basic list spread sheet showing various transaction columns A=date, B=description, C=income, D=expense and E=current balance, that is previous line total+income-expense, how do I get a fixed cell eg \$z\$100 equal to the latest line E cell regards

Belfix

Likes # 0

sorry..I meant to say how do I get the fixed cell to always use the latest E column figure automatically

Belfix

Likes # 0

sorry..I meant to say how do I get the fixed cell to always use the latest E column figure automatically

Woolwell

Likes # 0

Not quite sure what you mean. Do you want Z100 to equal E1 and then E2, etc? Or do you want the cell to always ref column E in which case \$E.

Picklefactory

Likes # 0

If you want to use cell range Z100 to equal the value of the last occupied cell in column E then in Z100 place

=INDEX(E:E,MATCH(9.99999999999999E+307,E:E))

Belfix

Likes # 0

Thanks Picklefactory...knew it was there somewhere - certainly works for me!

Belfix

Likes # 0

Thanks Picklefactory...knew it was there somewhere - certainly works for me!

Alan2

Likes # 0

Belfix, try one of these:

Assuming your income (col C) is shown positive and expenditure (col D) is also shown positive and the range of rows - not necessarily filled - runs from 5 to 101 then for cell Z100 the formula would be:

=sum(c5:c101)-sum(d5:d101)

Alternatively if the expenditure(col D)is shown negative then the formula in Z100 would be:

=sum(c5:d101)