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.
____________________________________________________________________________

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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)
>

On Jul 9, 11:59 pm, "Vasant Nanavati" <vasantn AT aol DOT com> wrote:
> (Untested
>
> =OFFSET(Sheet1!A$2,7*(ROW()-2),0)
>
> Copy down as far as needed.
> ____________________________________________________________________________
>
> <scottpick...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> >I have data on a template (Sheet1) and I need to output some data to a
> > summary table (Sheet2). On Sheet1, every 7thcell(starting with A2)
> > is thecellI 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 adynamicformula to avoid having
> > to type the same formula over and over for a bunch of cells when only
> > one value changes with respect to thereference. This wouldn't be a
> > problem if my values I want toreferencewere 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)

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


--
Regards,

Peo Sjoblom

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Jul 9, 11:59 pm, "Vasant Nanavati" <vasantn AT aol DOT com> wrote:
>> (Untested
>>
>> =OFFSET(Sheet1!A$2,7*(ROW()-2),0)
>>
>> Copy down as far as needed.
>> ____________________________________________________________________________
>>
>> <scottpick...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> >I have data on a template (Sheet1) and I need to output some data to a
>> > summary table (Sheet2). On Sheet1, every 7thcell(starting with A2)
>> > is thecellI 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 adynamicformula to avoid having
>> > to type the same formula over and over for a bunch of cells when only
>> > one value changes with respect to thereference. This wouldn't be a
>> > problem if my values I want toreferencewere 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)
>
> 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
>