Gilles...Anyone...Excel skip formula

[grayth]

Hey Gilles or Anyone?

I've been using the following formula

=MATCH(a3,"649history!d:d"),0)-3

to determine when the last hit was for the value found in cell a3 for example the reason for the -3 at the end is becuase i have 2 rows of column headers and if a number hit in the last draw i want to record a 0.

But what my question is I can't figure out how to manipulate this formula to continue the skip count to create in effect a skip chart showinghow many rows since that occurance backwards until it finds the next occurance.

It gets even worse as the formula won't work for a number in any position as the match formula will get screwed up over more than 1 column.

Any ideas? Thanks
Grayth

 

[grayth]

For those interested I found out a way please see below..

It gets around the problem of the match formula and allows a way to find and update the range to continue to count skips till
the next hit

column headings in large single letters

A B
1 =ADDRESS(COUNTA(A:A),1,4) gives the cell ref of last data
2
3
4 C
5 =MATCH(1,A1:A15,0)-1 counts the number of rows till a hit
6 D
7
1 =IF(C2=0,C2+2,C2) adds 2 to the total of rows
9 E
10 =ADDRESS(D2,1,4,1) finds the cell ref for the new row total
11 F
1 =MATCH(1,INDIRECT("sheet1!"&E2&":"&B1),0)-1
13 continues the count for the next hit by subsit the new cell
14 references
1


I know its a little confusing but If only I could post my excel sheet in html...I can but the code is longer than allowed sorry.

once you see the pattern I'm developing in the formula construction you just rinse and repeat for as many skip/hits you want.


Later Grayth