Hi Larry ,
Let us start from the formulae in columns AX , AY , AZ and BA.
These are dependent on the cells in columns W , X , Y and Z. Let us take just one column , and work our way through the dependencies.
If we take the cells in column W , the formula is dependent on column BI. BI is not just dependent on BD , but is identical to it , which means we can ignore the cells BI , BJ , BK and BL , and concentrate instead on the columns BD , BE , BF and BG.
The formula in column BD is dependent on column C , which in turn is dependent on column K.
Columns K , L , M and N consist of either blanks or 1s ; these are dependent on a formula which involves an external workbook ; since the path of the workbook is also included in the formula at present , understanding the formula is a bother ; even after the pathname of the workbook is removed , the formula is a monster one , and it is very unlikely that I can make any sense of it.
Also given the little data , I doubt that I can find any pattern in the 1s and blanks. Probably if we had a file with a thousand rows of data , and if the pattern repeats , it might be possible.
What is clear is the following :
As long as there are blanks in column K , column C will repeat its earlier digit ; thus if we start with the digit 0 , since the cell K51 is blank , C51 is 0.
When the cell in column K changes to 1 , the cell in column C increments to 1 ; as long as column K remains blank , column C will remain at 1 ; as and when column K changes to 1 , column C increments to 2.
This pattern of incrementing column C each time column K has a 1 in it continues for ever.
Column BD reflects column C as long as column C is in single digits ; the moment column C changes to a 2 digit number , column B will be the sum of these two digits reduced to a single digit ; thus a 10 in column C becomes a 1 in column BD ; a 17 in column C becomes a 8 in column BD ; possibly a 57 in column C will become a 3 in column BD ( 5 + 7 = 12 , 1 + 2 = 3 ).
Thus the value in column BD will remain the same as long as the value in column K is a blank ; when there is a 1 in column K , the value in column BD will increment ; however , each time the value was 9 and incremented , it becomes 1.
The value in column W reflects the value in column BD exactly.
I do not know why 3 sets of columns have been used , when they mirror each other :
W , X , Y and Z are the same as BI , BJ , BK and BL , and these in turn are identical to BD ,BE , BF and BG.
Anyway , let us start with the first row of data in columns AX , AY , AZ and BA ; these are all 1 to start with.
When a value remains the same in column C ( i.e. when the value in column K is a blank ) , the value in column AX increments ; when ever a value changes in column C ( i.e. when a value in column K is 1 ) , the value in column AX remains the same.
The values in columns K , L , M and N can have only a single 1 in one row i.e. 2 cells in the same row in these 4 columns can never be 1.
This means that in every row in columns C , D , E and F , only one cell can change.
Therefore , in every row in columns AX , AY , AZ and BA , all 4 cells will change ; of these 4 , 3 cells will increment , while the 4th cell will reset to 1.
The column which resets will be the column which changes in the 4 columns C , D , E and F.
For example , if column C changes , column AX will reset to 1 , while columns AY , AZ and BA will increment.
If column D changes , column AY will reset to 1 , while columns AX , AZ and BA will increment.
If column E changes , column AZ will reset to 1 , while columns AX , AY and BA will increment.
If column F changes , column BA will reset to 1 , while columns AX , AY and AZ will increment.
Narayan
