Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|