offset.... I Think!

%3CLINGO-SUB%20id%3D%22lingo-sub-2001605%22%20slang%3D%22en-US%22%3Eoffset....%20I%20Think!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2001605%22%20slang%3D%22en-US%22%3E%3CP%3ECell%20FU71%20References%20R71.%26nbsp%3B%20Cell%20FV71%20needs%20to%20reference%20X71.%26nbsp%3B%20Cell%20FW1%20needs%20to%20reference%20AD71.%26nbsp%3B%20How%20do%20I%20create%20a%20formula%20to%20drag%20over%20that%20it%20references%206%20cells%20away%20each%20time.%26nbsp%3B%20Right%20now%2C%20when%20I%20Drag%20it%20from%20FV71%20which%20has%20formula%20%3Doffset(r71%2C0%2C6)%20to%20FW1%20it%20changes%20it%20to%20offset(s71%2C0%2C6)%20which%20it%20really%20needs%20to%20be%20the%20formula%20%3Doffset(x71%2C0%2C6).%26nbsp%3B%20The%20values%20I%20am%20trying%20to%20find%20are%206%20columns%20apart%20and%20I%20am%20trying%20to%20condense%20them%20down%20next%20to%20each%20other%20in%20the%20FU%20to%20GR%20columns.%26nbsp%3B%26nbsp%3B%20Secondary%20question...%20if%20i%20just%20do%20a%20direct%20reference%20(FU71%3DR71)%20drag%20over%2C%20hide%20the%20unnecessary%20columns%2C%20can%20I%20pull%20a%20line%20graph%20off%20of%20that%20or%20will%20it%20pull%20all%20of%20the%20hidden%20columns%20data%3F%20%26nbsp%3B%20Thanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2001605%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2001698%22%20slang%3D%22en-US%22%3ERe%3A%20offset....%20I%20Think!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2001698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F906504%22%20target%3D%22_blank%22%3E%40excelmenot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20FU71%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(71%3A71%2C18%2B6*(COLUMN(FU71)-COLUMN(%24FU%2471)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20hide%20columns%2C%20Excel%20will%20hide%20the%20corresponding%20data%20in%20the%20chart%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Cell FU71 References R71.  Cell FV71 needs to reference X71.  Cell FW1 needs to reference AD71.  How do I create a formula to drag over that it references 6 cells away each time.  Right now, when I Drag it from FV71 which has formula =offset(r71,0,6) to FW1 it changes it to offset(s71,0,6) which it really needs to be the formula =offset(x71,0,6).  The values I am trying to find are 6 columns apart and I am trying to condense them down next to each other in the FU to GR columns.   Secondary question... if i just do a direct reference (FU71=R71) drag over, hide the unnecessary columns, can I pull a line graph off of that or will it pull all of the hidden columns data?   Thanks for your help!

2 Replies

@excelmenot 

In FU71:

 

=INDEX(71:71,18+6*(COLUMN(FU71)-COLUMN($FU$71)))

 

Fill to the right.

 

If you hide columns, Excel will hide the corresponding data in the chart too.

@Hans Vogelaar.  Worked.  Thank you so much