help identifying the first and last populated cells in any given row

[tbablue]

In row 2 of sheet 1 ($J$2:$AZ$2) I have some date values (200901,200902,200903,etc) as column headers. Subsequent rows ($J$3:$AZ$100) contain figures pertinent to the associated column headers - not all cells are populated, in fact most are blank. Column 'I' contains a list of names.

A simple Gantt or ECWP; tasks down the side - weeks along the top.

In column A I need a formula that returns the corresponding date value for the first populated cell in the row.

In column B I need a formula that returns the corresponding date value for the last populated cell in the row.

Can anyone help me with a formula for this? I can manufacture a long winded solution - I was hoping somone might know a 'cleaner' way to do this.

 

[jbeaucaire]

In A3, enter the following array formula:

=INDEX($J3:$AZ3,MATCH(TRUE,$J3:$AZ3<>0,0))

In B3, a second array:

=INDEX($J3:$AZ3,MATCH(2,1/($J3:$AZ3<>"")))

Array formulas are entered by pressing CTRL-SHIFT-ENTER to get the braces { } to appear around the formulas. If you just press ENTER, it will won't work.

After activating the arrays, the formulas can be copied down.

 

[SteveO59L]

Try posting a sample of your data

 

[tbablue]

200901 200902 200903 200904 200905 200906 200907 200908
TASK 1
TASK 2 10 10 10 10
TASK 3
TASK 4
TASK 5 15
TASK 6
TASK 7 12 12 12 12 12
TASK 8
TASK 9 5 5 5
TASK 10
TASK 11

excuse the rudimentary table - i'm at work and cant install the HTML tool

consider - the list of tasks is column C. In column A, I need a cute way to look across the rows and return the corresponding column header for the first populated cell in that row. Similarly, in column B I need a cute way to look across the rows and return the corresponding column header for the last populated cell in that row.

Does this help?

 

[jbeaucaire]

The first formula is still an array, but the last one is easier since we can just use a FALSE status on a LARGE number to return the last value.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	First	Last	Tasks	200901	200902	200903	200904	200905	200906	200907	200908
2			TASK 1
3	200901	200904	TASK 2	10	10	10	10
4			TASK 3
5			TASK 4
6	200901	200901	TASK 5	15
7			TASK 6
8	200901	200905	TASK 7	12	12	12	12	12
9			TASK 8
10	200901	200903	TASK 9	5	5	5
11			TASK 10
12			TASK 11
Sheet3

After entering the first two formulas, just copy down.

 

[Peter_SSs]

Or maybe this (no array formula).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	First	Last	Tasks	200901	200902	200903	200904	200905	200906	200907	200908
2			TASK 1
3	200901	200904	TASK 2	10	10	10	10
4			TASK 3
5			TASK 4
6	200901	200901	TASK 5	15
7			TASK 6
8	200901	200905	TASK 7	12	12	12	12	12
9			TASK 8
10	200901	200903	TASK 9	5	5	5
11			TASK 10
12			TASK 11
First& Last