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

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?

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

Hi!

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


Biff

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

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

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("Y t",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 ,
VLB 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?

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

In article ,
Domenic 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!