#REF! error when copying the formula from 1 column to the others

[ripper159]

Dear Experts,

I have my formula as below:
=if(And($D7<=reference!$C$3:$C$212;$E7<=reference!$D$3:$D$212;$F7<=reference!$E$3:$E$212;$G7<=reference!$F$3:$F$212;$H7<=reference!$G$3:$G$212;
$K7<=reference!$H$3:$H$212;Match(J$1;reference!$B$3:$B$212));reference!$A$3:$A$212;"")
This formula works for the first column, but when copying the formula from the first column to the others, it runs into #REF! error.

Is there any solution to this? I've searched on the internet but haven't found the problem. Appreciate your helps.
p.s. this is my sheet:
https://docs.google.com/spreadsheets/d/1s4pNW8R7o6839GvF7vennHlW8ppm81wzaFAmrRXJtao/edit?usp=sharing

Thank you.

 

[ghrain22]

When You select the cell, in the formula bar, what reference is giving you an error(#REF)? If you're only moving to other columns (and your rows are the same), I'd guess the J$1 is giving you the problem and hanging you off your sheet.

=if(And($D7<=reference!$C$3:$C$212;$E7<=reference!$D$3:$D$212;$F7<=reference!$E$3:$E$212;$G7<=reference!$F$3:$F$212;$H7<=reference!$G$3:$G$212;
$K7<=reference!$H$3:$H$212;Match(J$1;reference!$B$3:$B$212));reference!$A$3:$A$212;"")

 

[GorD]

Why does formula jump from $H7<=reference!$G$3:$G$212 to $K7<=reference!$H$3:$H$212 - what happened to I ?

When you copy this formula across from col J to K you'll get a circular ref in col K

 

[ripper159]

Why does formula jump from $H7<=reference!$G$3:$G$212 to $K7<=reference!$H$3:$H$212 - what happened to I ?

When you copy this formula across from col J to K you'll get a circular ref in col K

Thank you very much. I missed this point.
But it seems that the results from my formula are also wrong.
I just want the result returned from column A of reference sheet having the same character with the ones in the column where the formula is only appears in that column.