Start Date and End Date
 

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

Start Date and End Date
 

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

"Ananth" wrote:

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

Start Date and End Date
 

=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)

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