Thread: Customizing Auto-Fill

Is it possible to customize what changes in a formula when using the auto-fill option? I have the formula

=IFERROR(VLOOKUP(I9,sheet1!A1:B47,2,FALSE),"")

that I need to copy to multiple rows; but I only want I9 to change. However; when using auto-fill, it also changes A1:B47 to A2:B48, A3:B49, etc. Is there any way to make it so that only I9 changes when using auto-fill? Thanks for any help!

Hello,

yes, it's possible. The trick is to use absolute cell references.

In a formula like =trim(A1) the cell A1 is a relative cell reference. If you copy the formula to the right, it will become =trim(B1). If you copy it down it will become =trim(A2).

To make any part of the cell reference absolute (which means it will not change when copied), insert a $ sign in front of it

=A1 will change to =B1 if copied to the right. It will change to =A2 if copied down. Both the column and the row are relative references and will change when copied/filled down.

Now watch what the $ sign does in a cell reference.

=$A1 will become $A2 if copied down. If copied to the right, it will remain $A1. The column is fixed with the $ sign in front of the column letter. This is an absolute column reference.

=A$1 will remain A$1 if copied down. The row is fixed. If copied to the right, it will become B$1. The row is fixed with the $ sign in front of the row number. This is an absolute row reference.

=$A$1 copied across will remain $A$1. It will also remain $A$1 if copied or filled down. The $ sign fixes both the column letter and the row number. This is a complete absolute reference, for both column and row.

If you only want I9 to change in the formula, you need the $ sign in front of the rows and columns that you want to stay as they are:

=IFERROR(VLOOKUP(I9,sheet1!$A$1:$B$47,2,FALSE),"")

cheers,