3

Let's say that I have a value in cell A5 that is being used to calculate a value in A3.

Example:

A3 = (A1 + A2) * A5

When I copy paste cells A1:A3 and paste onto B1:B3, I'd like the resulting formula to be this:

B3 = (B1 + B2) * A5

Notice that A5 is still there.

It is the default behavior of Excel to cause this to be B5 instead of A5.

Is there a way to force a reference a single cell, such as A5, to be maintained while copy pasting rows?

Thanks

9

You want to use Absolute References then instead of relative references. Yes that is the default behavior of Excel. In your example do $A$5 to always reference A5.

A3 = (A1 + A2) * $A$5
B3 = (B1 + B2) * $A$5
  • 2
    To quickly turn an "A5" into a "$A$5" press "F4". This will help you scroll through fixed references: $A5 (Column fixed); A$5 (Row fixed); $A$5 (Column&Row fixed) – RocketGoal Jul 16 '10 at 14:15
2

In the formula, instead of referencing A5, reference $A$5. You can also keep only the column, but with changing row numbers, through referencing $A5, or only keep the column, changing row, by referencing A$5.

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.