Use just the rows and cols arguments of the offset function (leaving out the
height and width) to generate a single-cell reference.
=OFFSET(HeadCount!$B$1,COUNTA(HeadCount!$B:$B),0)

" wrote:

I have a spreadsheet that looks like this:
A B
Time In Time Out
12:15 PM 8:00 PM
2:00 PM 3:00 PM
4:00 PM 6:00 PM
6:00 PM 6:30 PM
8:00 PM 11:30 PM

I'm trying to use the formula: =OFFSET(HeadCount!$B
$1,0,0,COUNTA(HeadCount!$B:$B),1) to come up with the last value in
column B, which should be 11:30pm. However, I keep getting a #VALUE!
error. If I take out the COUNTA part and just use: =OFFSET(HeadCount!
$B$1,0,0,6,1) I still get the error. If I replace the Row value of 6
with 1 in this formula, it gives me the correct value of "Time Out".
Why won't it return the value when it is a time format? Can anyone
tell me what I'm doing wrong. COUNTA and OFFSET work fine
independently. Thanks for any help!

-Josh