It seems to me that you're trying to combine a vba range reference construct (Cells(2,x)) with a formula construct, or vice-versa, and I could be wrong, but I don't think it can be done. Have you considered utilizing the INDIRECT function? Try entering "Sheet2!J2:J564" into cell A1. Then in your formula, replace that value with "INDIRECT(A1)". I haven't tried it with your extensive formula, but it's worth a shot.