Dynamic Row Reference

[stamlin]

I have searched the forums to find the answer to this but can't find anything that answers the specific question...

To give some context I have one worksheet which looks at stocked quantities of component part numbers in a given week. This is the 'Sheet2' referred to in the example below. In this worksheet I have a column (column BR), which tells me whether we have zero quantities, thus preventing build going ahead (value in this case is YES) or whether we have no zero quantities and we are able to build (NO).

In another worksheet (let's say 'Sheet1'), I consider the build plan i.e. the quantities we would like to build in a week. I then use a lookup, looking at the 'Sheet2' worksheet to see whether we have any zero balances in that week.
If the returned value for zero quantities in a given week = YES, I want to look down the column to find the first instance that zero quantities = NO. Note the Sheet2 is set up with a row per week, hence needing to look down.

To do this I basically want to say "if zero quantities in week = YES, then look at the next week and keep looking until we see a NO".

The closest I've been able to find is the attempt below. It returns an error. In the statement INDIRECT(BR&P3) BR is the column, and P3 is a cell which contains the row number I'd like to use.

=MATCH("NO",'Sheet2'!(INDIRECT(BR&P3)):BR146,0)


It's difficult to articulate what I'm trying to achieve, so hopefully the above makes sense! I have attached a snip of the screen which help to explain the references.

Thanks in advance for any suggestions.

 

[KRice]

try this..

=MATCH("NO",INDEX($BR:$BR,P3):$BR$146,0)

or this if you don't want the intermediate calculation:

=MATCH("NO",INDEX($BR:$BR,O3+1):$BR$146,0)