Autofill

Contributor
Hi. I'm working on an Excel document. For its purposes sheet2 refers to sheet 1. So the formula it must follow is A1=Sheet1!A1, B1=Sheet1!A2, C1=Sheet1!A3, and so on. When I try to copy and paste the A changes. So A1=Sheet!A1 then B1=Sheet1!B1. Is there a way to prevent this?
1 Reply

Thomas,

Assuming I understood the challenge you're dealing with... that's how Excel behaves with regard to absolute vs. relative references. In your example A1 is a relative reference re. row (A) and column (1) numbers.

 

If you don't want the row number (A in your example) to change during a copy/paste operation (or other calcs) add a $ sign before A ($A1). $A1 is a reference where the row number is Absolute ($A) and column number (1) is Relative.

Same logic for the column number. If you want it to be Absolute add a $ sign before the 1 ($1). $A$1 is a reference where both the row and the column number are Absolute.

 

Finally you can have ref A$1 where the row number (A) is relative and the column number ($1) is absolute