• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

In Excel 2000 does anyone know how to.......

B

Bluto

Senior member
Does anyone know how, or if it's even possible, in Excel 2000, Win NT, to find the first blank cell in a column of data, or the last non-blank cell in that same column?

I have a spreadsheet where, in one column, I am entering an ongoing series of data (numbers). The column gets longer and longer and what I'd like to do is automatically pick the last value in the column and use it in a calculation.

As it is I have to enter the cell value of the number by hand, and while it's not crucial that I have this done automatically it's gotten to the point where I want to find out how to do it just because I've tried and tried and now it's an almost stubborn thing.😱

I've looked and looked and I can't find any way to do this. I know Excel pretty well, but this one has me stumped, and doing this would make the sheet more efficient. I've had to 'split' the screen so I can view the plot and still scroll the data column upward to add new entries. So I'm just trying to make the sheet easier to use and more efficient and get this out of my craw at the same time.

Anyone with an idea out there? It would be appreciated! 🙂

Thank you!

bluto
 
IF there are no empty cells in the column, CTRL-DownArrow will get you to the last cell used.

Then just DownArrow once to use the next empty cell.
 
Thanks for the idea. That's something I can do...no prob, and maybe I should make it more clear. I want to find a way to have Excel find this cell for me....if it's possible. Some kind of command or function that can look at a column and determine what the last nonblank cell is in a range in a column.

Thanks......😎

bluto
 
Try this for last non-blank cell:

=OFFSET(I1,COUNTA($I:$I)-1,0)

I1 = first cell in column with data
I = column with data
 
RayH.....THANK YOU! That was what I was looking for!

One thing tho....which is actually a good thing I think. In the formula, I had to change the "-1" to a "-2". I THINK what it was giving me was the first blank cell rather than the last nonblank cell, but no matter. It was an easy fix and it would have taken me till doomsday to find those commands in Excel's index. So now I know how to find either the first blank cell or the last nonblank cell in a column.

Thanks again!!

bluto 😀
 
Glad it worked. The only thing you want to be careful with is I used the entire column in the COUNTA function so if you had a title or header in that column and you didn't use that as the starting point in OFFSET, that would explain why you had to use a -2.

Lots of cool things you can do with OFFSET linked to sliding bars.


 
You must log in or register to reply here.

TRENDING THREADS

Back
Top