How do I get Excel to select a value at the bottom of a column of figures?

The column is always getting longer, as a row is added to the bottom of the table periodically.

Also, the spreadsheet is quite long as there are formulae within the table that runs on for lots of rows. When I print the spreadsheet Excel wants to print all of it, ie about 30 pages. How do I get it to just print only the part that is filled in?

Hopefully I hope you understand what I mean.

You can define a print area as a dynamic range, but that’s fairly advanced Excel-fu required.

A much simpler and auditable method is to create another sheet that is dynamically linked to the original showing only the data you want.

It is poor design to have the information you want to extract (eg. a total) for a column at the bottom of the column if data is to be added to that column regularly. Any summation or summary calculations should be located at the top of the sheet or in a second sheet.

I do, have you not got a summary total at the TOP of each Column instead of the bottom? far easier to view the totals at the top of the Column then you can print the the top row(s) only. Cleary these will require headings etc.
Sounds like a Pivot would work with the totals on the top row instead of the bottom..
Is this what you are looking for??? 😐

Selecting a value at the end of a range is fairly trivial with a bit of VBA and could probably be done without with a bit more information.

But it depends what you mean my “select” as to how you’d do it. Do you mean make it the active cell, use the value in the cell, refer to it in a formula etc etc.

Subtotals/totals above the data isn’t always a good idea either for a variety of reasons.

Really….. 🙄

Yes really!

Sometimes it’s fine, sometimes it’s the right thing to do, but other times it’s not! 🙄

OK, a bit more info for you all:

The spreadsheet has a row added at the bottom every 6 hours. It tracks the progress of our ship along its track and gets sent to our shore office staff. On each added row we put the date/time and the distance left to go. The various average speeds(overall, past 24hrs, past 6 hours), and arrival times (ETA’s) etc are all calculated automatically and shown in various cells at the top of the sheet.

So, I want the calculation of the Speeds and ETA’s to be based on the final (bottom) row, which contains the Distance To Go (DTG). I do have it working fine by using the MIN([range]) function on the column containing the DTG. Which is OK as long as we keep making progress and the DTG decreases. If we hove to/stop/divert due to weather, and the distance to go increases then things get screwy, as the MIN function will return to lowest value in the column, and that’s not always the true DTG.

This is a new document to replace a terrible one. I want it all to be as idiot proof as possible as I do work with a few. And there’s more of them ashore.

You can do it with a lookup fudge;

=LOOKUP(9.99999999999999E+37,B2:B1000)

where B2:B1000 is your range, it works because the function is looking for the huge number which it won’t find, and so it returns the last number in your column

I assume each new line has a date stamp in it, and that each additional line is sequential. So rather than use MIN on DTG, use MAX on Date stamp. And put it at the top of the spreadsheet.

jfletch, yes in v rare circumstances putting calc data the top might not be ideal, but in ratio to when it’s better to put summary calculations at the bottom of an array that changes dimensions I reckon it’s going to be at least 100:1 more appropriate at the top than not. Which I think is what bb was referring to. It’s a good rule of thumb of good practice in sheet design, whereas the alternative should only be for exceptional occasions. This isnt one of them.

Use COUNTA(“COLUMN REF”) to count the number of non-blank cells in the column that your numbers are in and then OFFSET to return the bottom value.
So say your values are in column B and start in B1, OFFSET(B1,COUNTA(B:B)-1,0) should do the trick.

Where column A contains the data:
=INDEX(A:A,(COUNTA(A:A)),1)

irelanst – Member
You can do it with a lookup fudge;

=LOOKUP(9.99999999999999E+37,B2:B1000)

where B2:B1000 is your range, it works because the function is looking for the huge number which it won’t find, and so it returns the last number in your column

Thanks irelanst, this is what I have done, sort of. I have added a left hand column, and have given each row a sequence number, then used a lookup form that. Might possibly be the safest way to do it.

jfletch, yes in v rare circumstances putting calc data the top might not be ideal, but in ratio to when it’s better to put summary calculations at the bottom of an array that changes dimensions I reckon it’s going to be at least 100:1 more appropriate at the top than not. Which I think is what bb was referring to. It’s a good rule of thumb of good practice in sheet design, whereas the alternative should only be for exceptional occasions. This isnt one of them.

Rob disagrees. Keep data discrete – on one sheet; calculations on another.

I agree, preference is for calcs on separate sheet.
I was referring to a preference if you end up putting calcs on a data sheet – (almost) never at the bottom