Date lookup

[DMP84]

Hey all,

I have an idea I would like to use in Excel but not sure if it's possible. I have a list of dates in row 1, in row 2 I have number of employees scheduled for that day, for example:

4/1/2010 4/2/2010 4/3/2010 4/4/2010 and so on for a month...
0 2 0 5

On the summary page I would like to show the first date of the month where someone is scheduled, in this case 4/2/2010.

Is this something that can be accomplished?

Any tips would be greatly appreciated.

[daddylonglegs]

Try this formula, assuming data is in a sheet called data

=LOOKUP(2,1/(data!2:2<>0),data!1:1)

[DMP84]

Thank you for the reply.

I am having some trouble understanding the above formula. It seems this formula is looking up the value 2, but the data can change, for next month can be 4 5 6 under corresponding dates. Hope it makes sense.


To clarify I have a sheet called Totals (which is the summary sheet, it's the first sheet) and the 2nd sheet is called Labor (with the dates and all the data pretty much). In the Labor sheet the dates are in cells G11 through AJ11, and the total man power is in G48 through AJ48. The values in G48:AJ48 will vary all the time, but wherever the first time there's a value >0 I want to use that date in the Totals sheet.

[daddylonglegs]

Sorry, I suggested the wrong formula, that will give you the last date in the month with a non-zero value, try this for the first date

=INDEX(data!1:1,MATCH(TRUE,INDEX(data!2:2<>0,0),0))

[DMP84]

Please see above, I just edited my response. Thank you I appreciate all the help.

[daddylonglegs]

You should be able to use the formula I suggested with the cell references changed accordingly, i.e.

=INDEX(Labor!G11:AJ11,MATCH(TRUE,INDEX(Labor!G48:AJ48<>0,0),0))

[DMP84]

Wow that's terrific it's perfect. I am trying to understand the formula so I know exactly what's going on, is there any way you would be able to assist in this? I am extremely grateful thank you!