Copying formul down a column. Data source keeps changing

[lauren18]

Hi,

I want to copy a formula down a columns to about 200 cells.

This is the formula;
=IFERROR(VLOOKUP(I5,A5:C330,2,0),0)

The only thing that should change is the row for I. So it should look like this
=IFERROR(VLOOKUP(I5,A5:C330,2,0),0)
=IFERROR(VLOOKUP(I6,A5:C330,2,0),0).
=IFERROR(VLOOKUP(I7,A5:C330,2,0),0)
=IFERROR(VLOOKUP(I8,A5:C330,2,0),0) Etc

However when I copy this formula down the column (Column J) the data source changes too

=IFERROR(VLOOKUP(I5,A5:C330,2,0),0)
=IFERROR(VLOOKUP(I6,A6:C331,2,0),0)
=IFERROR(VLOOKUP(I7,A7:C332,2,0),0)
=IFERROR(VLOOKUP(I8,A8:C333,2,0),0)

How do I make it change only the cell for I. It is too much to manually change the formula 200 times.

Please help!

 

[Sixthsense]

Use absolute references in your formula to protect the source table range (with $ Symbol).

=IFERROR(VLOOKUP(I5,$A$5:$C$330,2,0),0)



Relative And Absolute References In Formulas

This page describes relative and absolute cell references in Excel formulas.

Excel accepts cell references in what are called absolute and relative ranges. Absolute ranges have a $ character before the column portion of the reference and/or the row portion of the reference. Relative ranges do not use the $ character. The $ character indicates to Excel that it should not increment the column and/or row reference as you fill a range with a formula or as you copy a range. For example A1 is a relative range, while $A$1 is an absolute range. If you enter =A1 in a cell and then fill that cell down a column, the '1' in the reference will increment in each row. Thus, the formula in row 50 would be =A50. However, if you enter =$A$1 in a cell and fill down, the range reference will remain $A$1 -- it will not increment as you fill or copy down a column.
There are three absolute styles:

<tbody>
[TD="class: main, align: center"] Reference Style [/TD]
[TD="class: main"] Meaning [/TD]

[TD="class: main, align: center"] $A$1[/TD]
[TD="class: main"]Both the column and row reference are fixed. Neither will be incremented or changed during a copy or fill operation.
[/TD]

[TD="class: main, align: center"] $A1[/TD]
[TD="class: main"]Only the column reference is fixed. It will not change during a fill or copy, but the row will change.
[/TD]

[TD="class: main, align: center"] A$1[/TD]
[TD="class: main"]Only the row reference is fixed. It will not change during a fill or copy, but the column will change.
[/TD]

</tbody>

If you select all or part of a formula in the formula, you can press F4 to cycle range reference between the 4 styles (1 relative and 3 absolute).
Even with an absolute referencing style, Excel will still change row and column references when you insert a row or column. To have a truly absolute cell reference that will not change under any circumstances, use the INDIRECT function. For example =INDIRECT("A1") will always refer to cell A1, regardless of any changes made to the worksheet. This works because Excel does not interpret the string "A1" as an address. Instead, it treats it as plain text and therefore does not change it.
A common use of mixing absolute and relative range specifications is to create a running total of a column of number. For example, if you have data in cells A1:A10, the formula =SUM(A$1:A1) in cell B1 and filled down to cell B10 will return the running total for the numbers in column A.

Source:-
Relative And Absolute Range References