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

try
=OFFSET(O1,COUNTA($b:$b)-1,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
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

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

=OFFSET(HeadCount!$B$1,COUNTA(HeadCount!$B:$B)-1,0)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
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

Thanks that worked. But, now I'm not sure if I'm going about this the
right way. I want to use the formula as a named function and use that
as the source date of a chart I created. I'm trying to have the chart
update based on what the last row of data is, instead of just
arbitrarily using an ending row. So, instead of using something like
(=HeadCount!$A$2:$A$6) in the source data for the series, maybe it
would be (=HeadCount!TIME), if TIME was the name of the formula I was
orginally using. Not sure if this works like was orginally thinking.
Any ideas? THANKS