Overwrite a cell in another worksheet when the value gets updated

[Nlhicks]

This code does what I want it to do on the front sheet "Sheet1" however, what I really want is for it to take the "F8" value if not zero and update the spreadsheet "Sheet2".
If Range("F8").Value > 0 Then
Range("C8").Value = Range("F8").Value

The problem is, there are four separate criteria to meet in order to find the right cell modelled by this equation:
=INDEX(Sheet2!B:B,SMALL(IF(ISNUMBER((SEARCH(Sheet1!C3,Sheet2!J:J))*(SEARCH(Sheet1!C4,Sheet2!K:K))*(SEARCH(Sheet1!C5,Sheet2!L:L))*(SEARCH(F4,Sheet2!M:M))),MATCH(ROW(Sheet2!J:J),ROW(Sheet2!J:J)),""),ROWS(Sheet2!$A$1:$A$1)),COLUMNS(Sheet2!$A$1:A1))

How can I make Range ("C8") turn into the equation above so the appropriate cell on the appropriate spreadsheet will get changed? Once it does get changed then the visible sheet will show the change but the formula on the front sheet will stay and not get over written.

I keep getting this error

Method ‘Range’ of object ‘_Global’ failed​