If Formula to Enter in Last Date that inventory was received

[jjm3066]

Lets say in Row 1, I have every other column with a date as the header. In Row 2 under in each column of the date above, I have listed a number of how much inventory was received. How do I build a formula, that says look back Row 2 in msot recent order, and then place the date of Row 2 when the date is greater than 0. Pretty much tell me the most recent date (cell in Row 1) when inventory was received, and stop and palce that date.

 

[T. Valko]

Lets say in Row 1, I have every other column with a date as the header. In Row 2 under in each column of the date above, I have listed a number of how much inventory was received. How do I build a formula, that says look back Row 2 in msot recent order, and then place the date of Row 2 when the date is greater than 0. Pretty much tell me the most recent date (cell in Row 1) when inventory was received, and stop and palce that date.

Maybe this...

Book1
	B	C	D	E	F	G	H
1	8/1/2011	_	8/2/2011	_	8/3/2011	_	8/4/2011
2	10	_	20	_	_	_	_
Sheet1

=LOOKUP(1E100,B2:H2,B1:H1)

Format as Date

Returns 8/2/2011

 

[jjm3066]

I have zeros, in your last two columns, and it is bringing the last date which holds a zero, anyway to write and say dont mind the zeros?

 

[Aladin Akyurek]

Lets say in Row 1, I have every other column with a date as the header. In Row 2 under in each column of the date above, I have listed a number of how much inventory was received. How do I build a formula, that says look back Row 2 in msot recent order, and then place the date of Row 2 when the date is greater than 0. Pretty much tell me the most recent date (cell in Row 1) when inventory was received, and stop and palce that date.

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,IF(MOD(COLUMN(A2:N2)-COLUMN(A2),2)=0,1/A2:N2),A1:N1)

 

[T. Valko]

I have zeros, in your last two columns, and it is bringing the last date which holds a zero, anyway to write and say dont mind the zeros?

Try this...

Book1
	B	C	D	E	F	G	H
1	8/1/2011	_	8/2/2011	_	8/3/2011	_	8/4/2011
2	10	_	20	_	0	_	0
Sheet1

Assuming the "in between" cells don't contain numbers.

=LOOKUP(1E100,1/(B2:H2),B1:H1)

Format as Date