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!

I have not tried to grasp your formula, but you might want to take a look at the attached.

If my example doesn't help I think you should upload a file showing what you want.

Attached Files

Orientation.xlsx (8.7 KB, 10 views)

Hopefully I can clarify.

When I drag the formula down to populate the rest of the spreadsheet, it drags the exact formula down instead of changing the column reference. I know currently it shows as $D$5 but even when I change it to D$5, the column reference doesn't change (because the data I'm getting the information from is setup with each product as a column and the sizes as rows. I'm trying to copy information into a section that is orientated with the sizes as columns and the product as rows.

I was just hoping there was a way to have it "move" the column reference as I drag the formula down instead of having to manually change it to the column I need.

Does that help?

Not to me.

In your first message you wrote:

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

My formula does exactly what you ask for above.

My apologies,

I didn't quite understand your response at first. I used the formula you provided and it worked.

Thank you very much--you have saved me a lot of time.

Cheers!