Absolute cell references...changing?

Hi, just a quick question really, I have a feeling it can probably be resolved quite quickly but I just don't know how.

Basically, I have a formula containing absolute cell references, but whenever I insert a new row into the table that the reference is looking at, the cells bound by the absolute reference change.

I thought the point of using absolute cell references is that they don't change?

Anyway, is there some way around this, to avoid the cell reference changing when a row is inserted?

Thanks in advance for any help.

The only way is to indirectly refer to the cells.. via an Indirect() function or Index() function...

We would need to know more specifically what the cell ranges and formulas are...

Perhaps upload a sample workbook.

One solution for this specific situation that doesn't require changing your formula is to make the first row of cells in your lookup range blank, let's say that's A12:J12. Then if you need to insert a new row at the top of the lookup range, insert it below the blank line. That will automatically cause the range in your formula to grow by one line to include the new data, but still start in the correct place.

I thought the point of using absolute cell references is that they don't change?

Not exactly. It means it's an absolute reference, compared to a relative reference. It means that if you copy that reference to another place, it will still refer to the same location (that is, a reference to $D$24 will refer to $D$24 no matter where you copy it to). With a relative reference, if you copy it someplace else, it will refer to a different location that is in the same position relative to the referencing cell (that is, if you have a formula in A1 that refers to B2, then copy it to F6, the formula will be updated to refer to G7). An absolute reference will be updated by Excel if you add or delete rows or columns to ensure that it still refers to the same data, even though the address changes.

Does this work?

=VLOOKUP(B12, INDEX('Item & Stock'!$A:$A,12):INDEX($J:$J,29), 3)