Dynamic Cell Reference
 

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)

Dynamic Cell Reference
 

(Untested:)

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

Copy down as far as needed.
__________________________________________________ __________________________

wrote in message
ups.com...
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)

Dynamic Cell Reference
 

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.
__________________________________________________ __________________________

wrote in message

ups.com...

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

Dynamic Cell Reference
 

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

wrote in message
ups.com...
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.
__________________________________________________ __________________________

wrote in message

ups.com...

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