last number in column x

[beesus311]

Hi - I need a function that looks to the last entry in a column.

It's for a bank balance. I need to find my closing balance for each month, but due to the differing amount of transactions in each month, the last entry in the balance column falls on a different row each time.

How do I do this?

[NBVC]

=Lookup(9.99999999e+307,A:A)

where A:A represents column to search.

[VBA Noob]

Link has many examples

http://www.xldynamic.com/source/xld.LastValue.html

VBA Noob

[beesus311]

=LOOKUP(9.99999999E+307,F:F)

GREAT that did it. How do I change that to look to the first number in a column between a particular range?

[NBVC]

not as simple....

try:

=INDEX(I2:I10,MATCH(TRUE,ISNUMBER(I2:I10),0))

where I2:I10 is the range to look within....

Note: After adjust the ranges to suit your data, you must confirm the formula with CTRL+SHIFT+ENTER not just ENTER...you will see { } brackets appear around the formula...it is an array formula.

[beesus311]

Good skills!

One last one for you (for now at least)

Can I set up a formula in a cell (G9) that looks to the cell to it's right (G10). If there is a "D" in G10 then G9 becomes red?

I need to be able to paste different values into each of these cells after the formula is entered.

Thanks in advance

[NBVC]

You need to use conditional formatting to change colours....

Select G9 and go to Format|Conditional Formatting...

select Formula Is from 1st drop down and enter =$H9="D" (assuming you meant H9 as this is to the right of G9)

click Format and choose your colour scheme..

Click Ok.

Click Ok again to finish.

[beesus311]

OK that did it, and thanks for spotting my mistake in my question. Is there a way to apply this to other cells without having to re-type the reference cell into the formula (i.e =$H9="D" becomes =$G22="D")

Can the formula be modified to always look to the right one cell?

[NBVC]

If you take out the $ sign then the conditional format can be "carried" to other places in the sheet and always reference the cell to the right.

After you take out the $ sign from $H9 copy the cell with the conditional format and select the cell or cells you want to copy to. Go to Edit|Paste Special and select Formats. Click Ok.

[beesus311]

OK. I know I've asked a few since I said it was my last but this has just cropped up as a result of the ones we've done.

I now need to combine =LOOKUP(9.99999999E+307,CD:CD) with the conditional formatting you just explained.


I need the cell with the lookup formula to also be red if the cell it is looking at is red. (i.e If the bottom cell in the reference column is red)

Any ideas?

[NBVC]

In other words, are you saying that if the last number in the column has a "D" in the adjacent column, then colour the cell with the Lookup() formula red?

If so, then select the cell with the formula and activate the Conditional Format dialogue...

Select Formula Is and enter =INDEX(CE:CE,MATCH(9.99999E+307,CD:CD))="D"

click Format and choose colour.

Click Ok twice to finish.

[beesus311]

Thanks a lot for your help. I got pulled away before your last reply so have just got back to read it. Sounds good. I'm assuming it will work - you haven't let me down so far.

Thanks again

Beesus311