Pulling the last number from a column of

[stephenw]

Hi all, need some help

I need a formula which would take the last number that appears in a column and place it in A1.

The column is D10 through to D44 and the last number can appear in any one of those cells.

Thanks for looking and any help appreciated

StephenW

[Richard Schollar]

Hi Stephen

Try this:

=LOOKUP(9.99E+307,D10:D44)

Richard

[RobynC]

How about?

Please Login or Register  to view this content.

[stephenw]

Hmmm Richard, thanks for the help

I typed in the formula as suggested I get #N/A

this is what I have in the cells
=IF('13'!$B$2=0,E22,'13'!$B$2) F10 to F44
which gives me an ongoing end of day mileage reading.

Any other ideas?

[stephenw]

RobynC

Thanks for your idea, but where do I put this code etc??

[RobynC]

Hi Stephen.

You could assign it to a command button in the sheet, which if you click the command button will transfer the value. Have attached a sheet showing this approach.

I'm sure you could also put it in the worksheet so that it runs automatically whenever a value is evtered in that range but afraid by VBA isn't good enough to know how to do that.

Have a look at the attachment and if you have any questions get back to be.

Regards

Robyn

[RobynC]

Okay have had another look and have made it an automatic procedure.

Have attached a better version of the code, it's in sheet1 in the VBE, and will update the value in A1 whenever a change is made.

Hope this helps.

PS I doesn't work properly if there are any blank cells between the first and last values in the column. If you're likely to have blank cells let me know and will have another look. Am needing an excuse to do something different to what I am supposed to be doing!

[Richard Schollar]

Stephen

Where do these values reside? In your first post you indicated D10:D44, but in your second you suggested F10:F44 - so, if it is the F column, did you update the formula to:

=LOOKUP(9.99E+307,F10:F44)

Richard

By the way, what value is in E22?

[RobynC]

Hey again.

Tested Richard's method, and it works fine for me. Is better than mine as it still gives you the last value even if there are gaps

Robyn

[stephenw]

Sorry I had to get my daughter from the station..
Yes I made a mistake it is column F


I have attached the worksheet so you can have a look

E22 has =$F$21

Thanks

[Richard Schollar]

Given that file, couldn't you simply use =F44 in A1? That will always have the maximum value reached won't it? The Lookup is unnecessarily inefficient to determine exactly the same value.

EDIT: Oh, and your values are coming thru as Text which is why the lookup is failing currently.

Richard

[stephenw]

Richard at the moment the days are filled out fully as I'm running dummy data through, it wont always end on that last cell. All depends on what the driver does for the month they might only work for 3 days of the month on the road or 6 days etc etc...

[Richard Schollar]

Stephen

You would be best advised to ensure that the numbers that end up in Column F are genuinely numeric (they are currently text which can be shown by using a formula like:

=ISTEXT(F44)

). Once they are numeric, and given the nature of the number it will only ever increase down the column, then you could use:

=MAX(F10:F44)

which will pick up the greatest value in F10:F44.

Richard

[stephenw]

Thanks for all your help Richard & Robyn C


Regards Stephenw