2

I'm having a little bit of a problem and was wondering if your expert knowledge could help me out. Formula in question:

=if(and(E2=I1,F2<=J1),K1,if(and(E2=I1,F2>J1),K2,if(E2=I3,indirect("K"&2+(match(F2,J3:J7))),indirect("K"&7+match(F2,J8:J16)))))

Now the issue is I want to apply this formula to each row, allowing it to work out the cost of shipping a product. However I only want it to change certain parts of the formula, while keeping other cells intact. This is because some of the cells use data that is needed to solve the equation and if it shifts the cells down it screws the entire thing up. If you have any idea on how to solve this please let me know, I would really appreciate it.

1

Prepend the column letter and/or row number that you don't want to change with $.

Let me illustrate:

Let's say in cell B2 I have this formula

=B1*A1

If I copy that to C2, what is pasted in C2 is

=C1*B1

But that's not what I want. I always want to multiply against A1. If, instead, B2 was

=B1*$A$1

Then when I copied to C2 I'd get

=C1*$A$1

If I pasted into Z99 I'd get

=Z98*$A$1

This includes using auto-fill.

If you only want one of the coordinates to stay "static", just put the $ before it. Like

=B1*$A1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.