Find First Empty Cell/Get Last Value in a Column

[Bond00]

Is there a function to find First Empty Cell and Get the Last Value in a Column? I'd like to be able to do this without VBA if possible but if not that's ok, I'll just have to make it a .xlsm file and warn users to enable it when opening..

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]PCS1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Last Value[/TD]
[/TR]
[TR]
[TD]PCS2[/TD]
[TD][/TD]
[TD]PCS7[/TD]
[/TR]
[TR]
[TD]PCS3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PCS4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PCS5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PCS6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PCS7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to fill a cell (C2) with the last value of PCS7 and then also fill another cell (D2) with the value that would come next "PCS8"
If there is a function to do this let me know!

Otherwise if its not really possible without using vba please show a simple script. thanks!

 

[Weazel]

something like...

=LOOKUP("zzzzz",A:A)

 

[Joe4]

Let's say that your values are in column A.
Here is the formula to get the last value out of column A:

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

For explanation, see: https://exceljet.net/formula/get-value-of-last-non-empty-cell

To get the next value, I am making the assumption that you always have a three character prefix. If that is not correct, you need to explain the rules for the prefixes in more detail:

=LEFT(C2,3) & RIGHT(C2,LEN(C2)-3)+1

EDIT: I like Weazel's lookup formula better, its a little simpler.

 

[Scott T]

Excel 2010[TABLE="class: grid, width: 500"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]PCS1[/TD]
[TD="align: right"][/TD]
[TD]Last Value[/TD]
[TD]next value[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]PCS2[/TD]
[TD="align: right"][/TD]
[TD]PCS7[/TD]
[TD]PCS8[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]PCS3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]PCS4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]PCS5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]PCS6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]PCS7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=LOOKUP("ZZZZZZZZZZZZZZZZZZZZZZZZZZZ",A1:A27)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=LEFT(C2,3)&RIGHT(C2,LEN(C2)-3)+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]