The other day a member emailed me the following question:
“I created a check book in Excel 2010 and I was able to figure out the running balance which basically adds, subtracts accordingly and it gives me a running balance.
My question is; is there a way to display that running balance (considering it keeps changing rows with each new entry) in another cell?
I am trying to create a cell near the top of the worksheet that also displays my current balance and well as within the worksheet.
Let's assume Steve has a worksheet something like the example below, and he wants a formula that updates to find the last value in the 'Balance' column, like the one in cell C3:
It's actually quite easy. With some clever use of the INDEX and MATCH functions we can use the following formula:
I've also used this formula in cell B3 to get the latest date from column A, like this:
You can do the same for a row, just change the cell references to reference a row rather than a column. For example:
Finding the Last Text Value
The above formulas work if the data in your range is numeric.
But if it’s text you want to look up then you’ll need this formula:
I actually used this technique in my 'toughest Excel challenge ever' but I think it's a great technique to use on its own too.
Thanks for your question, Steve.