Excel boffins - need help on a problem

[Rob G.]

OK, imagine a list of data in a single column; e.g. A1-A50. Some of those cells may be blank, but the blanks will always come after the data.

I need to be able to get the value of the LAST in-use cell in the list. The last one that is not a blank.

I've written a macro to do it, but I would prefer to use a regular worksheet function if possible.

Any suggestions?

 

[NeoV]

you want one cell to contain a function that gives you this value, or a combination of cells/formulas?

 

[Rob G.]

It doesn't really matter. Obvioulsy a single cell would be preferable, but it's not that important.

 

[NeoV]

I can't think of a way to do this with a single cell formula/function.....but I can get this to work...

YOu could have a column next to your original list with =if(a2="",b1,a2) in each cell.

This would give you a value in every single cell in your new column (for B1 you would just have to have =a1, but the formula would work for the rest of the column).

Ok, so now you have two columns...your original column and a column right next to it. Lets say your original column is A2:a24, and you have 11 cells with numbers and 12 cells with blanks.

5 5
4 4
3 3
5 5
6 6
8 8
5 5
4 4
4
4
4 4
4
3 3
3
3
5 5
5
5
5
5
5
5
5

Ok, now you need a few formulas. You need a formula to count the number of cells in your range, so use =COUNT(A2:A24) to give you the number of cells in your original list with numbers in them, and use =COUNTBLANK(A2:A24) to give you the number of blank cells in your original list. Adding those two numbers together gives you the total number of rows in your list.....which also tells us how far down to go in an Hlookup formula to find the last value in the new column (B).

I then used =HLOOKUP(D6,B1:B24,D4,FALSE)...D6 is the word "Value", which is also the column header for column B.....D4 is the sum of the two count formulas listed above.


Probably an easier way to do this, but that is the first thing I thought of.

 

[NeoV]

This is odd...when I go to edit my message, it shows the columns in the correct order, but looking at the thread afterwards, it reverses the columns of data.....so the column with the blanks in it should be column A, and the column with all numbers is column B...not sure why it is doing that!