MIN(INDEX formula which ignores blanks or zeros?

[SHARRIS2013]

Hi i am using the below formula

=MAX(INDEX((Sheet1!A1:A59>=B1)*(Sheet1!A1:A59<=C1)*Sheet1!B1:B59,))

I tried to adapt it to use the MIN function but because my range covers periods in the future some cells are still blank, is there a way of using the formula to incorporate MIN but ignoring any blank or zero values in sheet 1 column B?

An example is
Sheet 1
A1-A59 runs from 01/12/2012 - 28/01/2013
B1-B59 only has values in upto 04/01/2013 as tomorrows data isnt available yet

Sheet 2
B1 is 05/12/2012
C1 is 20/01/2013

I want the fomula to use the dates in B1 and C1 to look at the values in sheet 1 column B and find the minimum withour returning a 0 value as the dates 05/01/2013-20/01/2013 have no values yet
tomorrow once i update my data i would need the formula to then take into account upto the 5th and continue each day up until the 20/01/2013 is reached

Thank you

 

[Andrew Poulsom]

For MIN you will need an array formula:

=MIN(IF((Sheet1!A1:A59>=B1)*(Sheet1!A1:A59<=C1)*Sheet1!B1:B59,Sheet1!B1:B59))

confirmed with Ctrl+Shift+Enter.

 

[SHARRIS2013]

Thats works great, again thanks for your help Andrew!