Hi Mike,

Many thanks for assistance. Still trying to find a workaround to layout point
raised below.
Could the MATCH function assist in finding the 1st leg of the variable value
and then use OFFSET for the 2nd leg of variable value.

Mike H wrote:
Sam,

I think Iv'e got it

Find a variable number x in column A
When found there must be a C in the row below
Match the variable number x rows above the C
The second variable number must also have a C in the row below

Yes

based upon your data any solution looking for a number higher than 3 will
fail because you can't search more than 3 rows back above Row 4 so to get
around this instead of looking back x rows as you ask my formula looks back
only 8 rows and I start in Row 16 to give some breating space..
My variable number is in D1

Trying to find a workaround. Stuck with the data layout, starting in row 4.

When you resolve the above problem you can change the formula

change -8 to
D1*-1

change -7 to
(D1*-1)+1

=IF(AND(A16="C",A15=$D$1,OFFSET(A16,-8,0)=$D$1,OFFSET(A16,-7,0)="C"),0,C15+1)

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1