Dec 18 2020 08:11 AM
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!
Dec 18 2020 08:33 AM
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.