

Thread Tools  Display Modes 
#1




Cell Ref does not automatically change when formula is copied because of data orientation
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,,,)*2OFFSET($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! 
#2




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. 
#3




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? 
#4




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. 
#5




My apologies,
I didn't quite understand your response at first. I used the formula you provided and it worked. Thank you very muchyou have saved me a lot of time. Cheers! 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Need a formula to copy cell data with its format  wfhin2016  Excel  12  04232016 08:13 AM 
Change formula cell range based on cell value  Scoth  Excel  4  10252012 07:51 AM 
Creating formula based on if data is correct in cell  MattMurdock  Excel  1  08062012 03:11 AM 
Automatically change the value of one cell so that two other cells become equal  matthew544  Excel  5  09182011 08:56 AM 
Copying data from one cell to another automatically  mrphilk  Excel  4  06102010 11:52 PM 