New Contributor

# offset.... I Think!

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

# Re: offset.... I Think!

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.

# Re: offset.... I Think!

@Hans Vogelaar.  Worked.  Thank you so much