Searching for first and last in a table to chart (gantt)

[VLB]

I am using a rather large table and need to search for the first and last
cell that has a value entered in a particular row and return the
cooresponding date in that column.

The table has a few columns showing task name etc that there is serveral
rows that represent a date. In each row a number is put in a column that
matches a date or is left blank.

I have tried to llustrate this below

__ |A1__|___B1___ |__C1_|__D1_|....|__E1_|__H1_|__I1_|__J1_|__K1__|
A2|Task | Sub Task |Phase| Name| |01/05| 02/05| 03/05|04/05| 05/05 |
A3| Xt Xs Yp Xn | null | 1 | null |
2 | null |
A4| Yt Ys Yp Yn | null | 1 | null |
2 | null |
A5| Zt Zs Zp Zn | 1 | null | 1 |
null | null |

what I need to do is search a row for the first cell that has data enteres
(it is positive numbers so >0 will do) and then return the corresponding date.
I then need to search the row to find the last cell in the row with an
number >0.

I am looking for 2 fomulas or if needed macros that will give me to values
(dates).

I am using these dates to plot a Gantt chart which seems to be working but I
cant seem to find the right way of searching for these dates.

Eg Using teh example of teh table I am using above, I need to find that
first and last date for Task Yt (A4). I need as formula/macro that will find
H4 as first date and J4 as last date and return H1 and J1 as the values


Can anyone help me?

 

[Biff]

Hi!

Are you specifically looking for "YT" and will there always be a first and
last date?


Biff

 

[Domenic]

Assuming that 'Null' means that the cell is empty, try the following...

First:

=INDEX(G2:K2,MATCH(TRUE,INDEX(G3:K5,MATCH("Yt",A3:A5,0),0)<>"",0))

....confirmed with CONTROL+SHIFT+ENTER

Last:

=LOOKUP(9.99999999999999E+307,INDEX(G3:K5,MATCH("Yt",A3:A5,0),0),G2:K2)

To exclude zero values...

First:

=INDEX(G2:K2,MATCH(TRUE,INDEX(G3:K5,MATCH("Yt",A3:A5,0),0)>0,0))

....confirmed with CONTROL+SHIFT+ENTER


Last:

=LOOKUP(9.99999999999999E+307,IF(INDEX(G3:K5,MATCH("Yt",A3:A5,0),0)>0,G2:
K2))

....confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

 

[Domenic]

Last:

=LOOKUP(9.99999999999999E+307,IF(INDEX(G3:K5,MATCH("Yt",A3:A5,0),0)>0,G2:
K2))

...confirmed with CONTROL+SHIFT+ENTER

Can be shortened...

=LOOKUP(2,1/(INDEX(G3:K5,MATCH("Yt",A3:A5,0),0)>0),G2:K2)

....confirmed with just ENTER.

Hope this helps!