Formula changes after cut and paste

[kgkev]

I am reporting on supplier rejects over a 3 months period. I have a list of events with supplier account numbers in column F.
I want to strip out the unique occurrences and rank them into a top 10.

this is my working formula as an array. (probably over complicated but it get the job done)

{=INDEX('Completed Rejects'!$F$1:$F$1000,MATCH(MAX(IF(LEN(SUBSTITUTE('Completed Rejects'!$F$1:$F$1000,"-",""))>0,IF(1-ISNUMBER(MATCH('Completed Rejects'!$F$1:$F$1000,$B$12:$B13,0)),COUNTIF('Completed Rejects'!$F$1:$F$1000,'Completed Rejects'!$F$1:$F$1000)))),IF(LEN(SUBSTITUTE('Completed Rejects'!$F$1:$F$1000,"-",""))>0,IF(1-ISNUMBER(MATCH('Completed Rejects'!$F$1:$F$1000,$B$12:$B13,0)),COUNTIF('Completed Rejects'!$F$1:$F$1000,'Completed Rejects'!$F$1:$F$1000))),0))}

At the end of the three months the data is cut and pasted to an archive sheet and new data builds up in the supplier reject sheet.

The problem is my formula changes after the cut and paste action. In some instances the "F1000" reduces by the number of cut cells. however this doesn't happen in every instance.


I figured I could surround the whole formula with an "indirect()" but do not think this is compatible with an array?


Any suggestions on how to stop this formula changing?

 

[jmacleary]

Hi there. If instead of cut you use copy and paste, and then clear the contents, the formula will remain unchanged.