I am working on a template for a project I am working on that seeks to reference information from a range of cells N2:U150 (Called Inventory).

My current template takes up cells A6:K18 and I want to be able to drag down this block of cells and have most of the fields autopopulate.

I have figured out how to make all the other cells auto-populate except for the most important cell.

I am using A6 as my primary reference cell. I need it to draw from N2:N150, sequentially.

The current formula I am using is:


This populates the first block of cells easily. However when I drag down the block of cells the formula becomes:
Which does not draw the correct data.

I need it to read:

I realize this is because as you drag down, the formula moves down to A15, A28, A41 etc. However, I cannot find a way to make Column A reference from Column N sequentially.

Is there any formula that I can use that will fix this problem?