# Help with an IF Formula

D

#### David

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?

L

#### lyle

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 thenew
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