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

"VLB" <[email protected]> wrote in message
news:[email protected]...
>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?
>

[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!

In article <[email protected]>,
VLB <[email protected]> wrote:

> 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?

[Domenic]

In article <[email protected]>,
Domenic <[email protected]> wrote:

> 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!