|
|
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,,,)*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! |
#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 much--you 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 | 04-23-2016 08:13 AM |
Change formula cell range based on cell value | Scoth | Excel | 4 | 10-25-2012 07:51 AM |
Creating formula based on if data is correct in cell | MattMurdock | Excel | 1 | 08-06-2012 03:11 AM |
Automatically change the value of one cell so that two other cells become equal | matthew544 | Excel | 5 | 09-18-2011 08:56 AM |
Copying data from one cell to another automatically | mrphilk | Excel | 4 | 06-10-2010 11:52 PM |