Start Date and End Date

[Guest]

Can help me to find out the cell ref that shows value last


A B C D E F E G H
1 Des Jan Feb Mar Apr May Jun Start End
2 Project-1 0 1 2 1 0 0
3 Project-2 1 1 1 1 1 1
4 Project-3 0 0 0 0 1 1

I want to see in Col G & H results as under :

Start Dt End Dt
Project-1 Feb Apr
Project-2 Jan Jun
Project-3 May Jun

Any help will be greatly appreciated

 

[Guest]

Copy to H2 and down:
=IF(B2<>0,$B$1,IF(C2<>0,$C$1,IF(D2<>0,$D$1,IF(E2<>0,$E$1,IF(F2<>0,$F$1,$G$1)))))
Copy to I2 and down:
=IF(G2<>0,$G$1,IF(F2<>0,$F$1,IF(E2<>0,$E$1,IF(D2<>0,$D$1,IF(C2<>0,$C$1,$B$1)))))

 

[Bob Phillips]

=INDEX($B$1:$G$1,MIN(IF($B2:$G2<>0,COLUMN($B2:$G2)-COLUMN($B2)+1)))

and

=INDEX($B$1:$G$1,MAX(IF($B2:$G2<>0,COLUMN($B2:$G2)-COLUMN($B2)+1)))

both are arry formulae, so commit with Ctrl-Shift-Enter, not just Enter

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)