Dynamic Cell Reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scottpickles@gmail.com - 10 Jul 2007 04:30 GMT

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)

Reply to this Message

Vasant Nanavati - 10 Jul 2007 04:59 GMT

(Untested:)

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

Copy down as far as needed.
____________________________________________________________________________

>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)
[quoted text clipped - 23 lines]
>
> Scott Pickles (a.k.a. Namelkcip <-> "Pickle Man" spelled backwards)

Reply to this Message

scottpickles@gmail.com - 10 Jul 2007 22:11 GMT

> (Untested:)
>
[quoted text clipped - 34 lines]
>
> > Scott Pickles (a.k.a. Namelkcip <-> "Pickle Man" spelled backwards)

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

Reply to this Message

Peo Sjoblom - 10 Jul 2007 22:25 GMT

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

Signature

Regards,

Peo Sjoblom

>> (Untested:)
>>
[quoted text clipped - 45 lines]
>
> Namelkcip

Reply to this Message

Rate this thread: