Can anyone assist me, I am attempting to write an IF formula, that will find
an = value in another worksheet and then do the calculation and post the new
value on my current worksheet. I am having problems when I copy the formula
down my column, it wants to follow down the column in the other worksheet.
Is there a way to have it lock on to the cells I wish it to look at in the
hidden worksheet using the $?

Example - I am working in Sheet2, the information I want is on Sheet 1 and
will never change - this is my formula

IF (B2=Sheet1!A1, +Sheet2!J1+Sheet1!A2, IF(B2=Sheet1!A2,
+Sheet2!J1+Sheet1!A2))

I want it to do this as I move down the column on Sheet2

IF (B2=Sheet1!A1, +Sheet2!J2+Sheet1!A2, IF(B2=Sheet1!A2,
+Sheet2!J2+Sheet1!A2))
IF (B3=Sheet1!A1, +Sheet2!J3+Sheet1!A2, IF(B3=Sheet1!A2,
+Sheet2!J3+Sheet1!A2))

but I am getting this

IF (B2=Sheet1!A1, +Sheet2!J2+Sheet1!A2, IF(B2=Sheet1!A2,
+Sheet2!J2+Sheet1!A2))
IF (B3=Sheet1!A2, +Sheet2!J3+Sheet1!A3, IF(B3=Sheet1!A3,
+Sheet2!J3+Sheet1!A3))

How do I lock the location for my Sheet 1 using multiple worksheets? Can
anyone help?

On 4 Jan, 00:28, David wrote:
Can anyone assist me, I am attempting to write an IF formula, that will find
an = value in another worksheet and then do the calculation and post the new
value on my current worksheet. *I am having problems when I copy the formula
down my column, it wants to follow down the column in the other worksheet. *
Is there a way to have it lock on to the cells I wish it to look at in the
hidden worksheet using the $?

Example - I am working in Sheet2, the information I want is on Sheet 1 and
will never change - this is my formula

IF (B2=Sheet1!A1, +Sheet2!J1+Sheet1!A2, IF(B2=Sheet1!A2,
+Sheet2!J1+Sheet1!A2))

I want it to do this as I move down the column on Sheet2

IF (B2=Sheet1!A1, +Sheet2!J2+Sheet1!A2, IF(B2=Sheet1!A2,
+Sheet2!J2+Sheet1!A2))
IF (B3=Sheet1!A1, +Sheet2!J3+Sheet1!A2, IF(B3=Sheet1!A2,
+Sheet2!J3+Sheet1!A2))

but I am getting this

IF (B2=Sheet1!A1, +Sheet2!J2+Sheet1!A2, IF(B2=Sheet1!A2,
+Sheet2!J2+Sheet1!A2))
IF (B3=Sheet1!A2, +Sheet2!J3+Sheet1!A3, IF(B3=Sheet1!A3,
+Sheet2!J3+Sheet1!A3))

How do I lock the location for my Sheet 1 using multiple worksheets? *Can
anyone help?

Hi David -

Use:

IF (B2=Sheet1!A$1, +Sheet2!J2+Sheet1!A$2, IF(B2=Sheet1!A$2,
+Sheet2!J2+Sheet1!A$2))


Best regards,

Lyle