I am using the sheet to show manning levels
so that it recognises any number

I don't imagine you'd have negative manning? So, assuming the numbers are
=0:

For the first number (if any):

=IF(SUM(A2:F2),INDEX(A1:F1,MATCH(1,INDEX(--ISNUMBER(A2:F2),1,),0)),"N/A")

For the last number (if any):

=IF(SUM(A2:F2),LOOKUP(1E+100,A2:F2,A1:F1),"N/A")

Format both as DATE

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
Sorry to bother you again Ron.

How would I change the formulas that you gave me (both for first value and
last value) so that it recognises any number rather than just a "1".
--
Regards
Matt


"Ron Coderre" wrote:

You're welcome, Matt....I'm glad I could help.


***********
Regards,
Ron

XL2003, WinXP


"Matt" wrote:

Thanks Ron

Works a treat.

--
Regards
Matt


"Ron Coderre" wrote:

Here you go....

Your example began in Col_A, so no offset adjustment was necessary.

With the new information,
try this:
=IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,
LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a")

or this (with a hardcoded offset of 10)
=IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,
LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Matt" wrote in message
...
Thanks Ron

The formula for the first value works fine however the formula for
the
last
value returns a #REF!.

The specific formula that I am using is as follows (different range
of
data
obviously)

=IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a")

What do you think?

--
Regards
Matt


"Ron Coderre" wrote:

With your posted data in A1:F4

This formula returns the data corresponding to the first 1 in
Row_2:
G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a")

....and this one returns the data corresponding to the last 1 in
Row_2:
H2:
=IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a")

Format those cells as Dates.
Copy those formulas down as far as you need.

In the above example,
G2 returns 01/07/2008
H2 returns 02/04/2008

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Matt" wrote in message
...
I have a spreadsheet with data as follows:

A B C D
E
F
1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08
02/11/08
2 1 1 1 1
1
3 1 1 1
1
1
4 1 1
1
1

I am using the sheet to show manning levels for each week of a
project.
Row
1 contains the dates of each week. The rows beneath contain a
"1" in
each
cell when an employee will be on site.

I am trying to lookup the first value in each row and return the
relevant
date from row 1 above (eg on row 3 the required result would be
01/14/08).
Similarly, I would also like to lookup the last value in each
row and
return
the date from row 1 above (eg on row 4 the required result would
be
02/11/08).

Any help with this would be greatly appreciated. Thank you.
--
Regards
Matt