Dynamic Cell Reference

  • Thread starter Thread starter scottpickles
  • Start date Start date
S

scottpickles

I have data on a template (Sheet1) and I need to output some data to a
summary table (Sheet2). On Sheet1, every 7th cell (starting with A2)
is the cell I want (i.e. non-contiguous) and I want to populate these
values into Sheet2, starting with B2 and continuing contiguous (i.e.
B3,B4,B5,etc) until all the values from Sheet1 are accounted for. As
another way of saying this, I'd like a dynamic formula to avoid having
to type the same formula over and over for a bunch of cells when only
one value changes with respect to the reference. This wouldn't be a
problem if my values I want to reference were contiguous.

=Sheet1!A2
=Sheet1!A9
=Sheet1!A16
=Sheet1!A23
..
..
..
..=Sheet1!A366

OFFSET and INDIRECT will not do this, or at least I can't see how. I
have tried using arrays, named ranges and INDEX to no avail. I
realize VBA is very powerful and could probably do this no prob, but I
have exactly ZERO VBA experience.

Regards,

Scott Pickles (a.k.a. Namelkcip <-> "Pickle Man" spelled backwards)
 
(Untested:)

=OFFSET(Sheet1!A$2,7*(ROW()-2),0)

Copy down as far as needed.
____________________________________________________________________________
 
(Untested:)

=OFFSET(Sheet1!A$2,7*(ROW()-2),0)

Copy down as far as needed.
____________________________________________________________________________
Click to expand...

I copied and tried that OFFSET function and it returned a value of 0
in all instances. I am curious why the formula has A$2 and not $A$2?
In any case, it doesn't work as is, but I might be able to modify it
so that it works. I realize it's tough to come up with a solution to
a problem that isn't right in front of you. If anyone has other
opinions, please post.

Thanks,

Namelkcip
 
You need to put the formula in row number 2, another way that you can put
anywhere

=OFFSET(Sheet1!A$2,7*(ROWS($A$1:A1)-1),0)


or even better

=INDEX($A$2:$A$65535,ROWS($A$1:A1)*7-6)


which is non volatile
 
Back
Top