Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-01-2016, 09:01 AM
Kristinmc Kristinmc is offline Cell Ref does not automatically change when formula is copied because of data orientation Windows 10 Cell Ref does not automatically change when formula is copied because of data orientation Office 2013
Novice
Cell Ref does not automatically change when formula is copied because of data orientation
 
Join Date: Aug 2016
Posts: 3
Kristinmc is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 08-01-2016, 10:05 AM
xor xor is offline Cell Ref does not automatically change when formula is copied because of data orientation Windows 10 Cell Ref does not automatically change when formula is copied because of data orientation Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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
File Type: xlsx Orientation.xlsx (8.7 KB, 4 views)
Reply With Quote
  #3  
Old 08-01-2016, 10:18 AM
Kristinmc Kristinmc is offline Cell Ref does not automatically change when formula is copied because of data orientation Windows 10 Cell Ref does not automatically change when formula is copied because of data orientation Office 2013
Novice
Cell Ref does not automatically change when formula is copied because of data orientation
 
Join Date: Aug 2016
Posts: 3
Kristinmc is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 08-01-2016, 10:29 AM
xor xor is offline Cell Ref does not automatically change when formula is copied because of data orientation Windows 10 Cell Ref does not automatically change when formula is copied because of data orientation Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #5  
Old 08-01-2016, 10:57 AM
Kristinmc Kristinmc is offline Cell Ref does not automatically change when formula is copied because of data orientation Windows 10 Cell Ref does not automatically change when formula is copied because of data orientation Office 2013
Novice
Cell Ref does not automatically change when formula is copied because of data orientation
 
Join Date: Aug 2016
Posts: 3
Kristinmc is on a distinguished road
Default

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!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Ref does not automatically change when formula is copied because of data orientation 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
Cell Ref does not automatically change when formula is copied because of data orientation 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
Cell Ref does not automatically change when formula is copied because of data orientation Copying data from one cell to another automatically mrphilk Excel 4 06-10-2010 11:52 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:22 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft