Hello,



I have a very long formula that at the moment, I cannot drag down and have it change the cell reference automatically. This is what it looks like now:

=IFERROR(CEILING(IF(OFFSET($D$5,COLUMNS($A:A)-1,,,)<OFFSET('Inventory Tolerances'!$D$5,COLUMNS($A:A)-1,,,)*2,OFFSET('Inventory Tolerances'!$D$5,COLUMNS($A:A)-1,,,)*2-OFFSET($D$5,COLUMNS($A:A)-1,,,),""),$D$41),"")

Whenever $D$5 is referenced,i need that to change to $E$5, $F$5, etc as the formula is dragged dow
The reason this is difficult is because my data is setup like this:

Product 1 Product 2 Product 3
Size 1 1 2 3
Size 2 4 5 6
Size 3 7 8 9

and i'm trying to orient it like this (Hence all the offset formulas):

Size 1 Size 2 Size 3
Product 1 1 4 7
Product 2 2 5 8
Product 3 3 6 9


I've considered using HLookup to find the cell refer((so it can change as it's populated down and across) but i couldn't get it to work.

Anyone have advice on how I can get this to work? This is a large spreadsheet so changing the cell reference manually will take a very long time.

Thanks!