I have a column of cells that import there values from another worksheet with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference data on
"sheet 2" is in a column with no spaces. If it were only a couple of entries
i would do it manualy but there are about 4500 of them. i was hoping to use
an "indirect" function but i don't seem to be able to copy the formula and
have the cell reference progress down the list, it copies the same cell
reference into all copied cells.

Assuming you are displaying your first result in row 1 and the next one in
row 7, try the following:

=IF(ROW()-(INT(ROW()/6)*6)=1,INDIRECT(CONCATENATE("Sheet2!A",(INT(ROW()/6)+1))),"")

Andrea Jones
www.allaboutoffice.co.uk

"Picman" wrote:

I have a column of cells that import there values from another worksheet with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference data on
"sheet 2" is in a column with no spaces. If it were only a couple of entries
i would do it manualy but there are about 4500 of them. i was hoping to use
an "indirect" function but i don't seem to be able to copy the formula and
have the cell reference progress down the list, it copies the same cell
reference into all copied cells.

actually the first cell is in row 3 and the next is in row 9 etc. i tried
your formula and it returned nothing.

"Andrea Jones" wrote:

Assuming you are displaying your first result in row 1 and the next one in
row 7, try the following:

=IF(ROW()-(INT(ROW()/6)*6)=1,INDIRECT(CONCATENATE("Sheet2!A",(INT(ROW()/6)+1))),"")

Andrea Jones
www.allaboutoffice.co.uk

"Picman" wrote:

I have a column of cells that import there values from another worksheet with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference data on
"sheet 2" is in a column with no spaces. If it were only a couple of entries
i would do it manualy but there are about 4500 of them. i was hoping to use
an "indirect" function but i don't seem to be able to copy the formula and
have the cell reference progress down the list, it copies the same cell
reference into all copied cells.

In that case use

=IF(ROW()-(INT(ROW()/6)*6)=3,INDIRECT(CONCATENATE("Sheet1!A",(INT(ROW()/6)+1))),"")

You just need to change the number before INDIRECT to whichever row you're
starting on.

Andrea

"Picman" wrote:

actually the first cell is in row 3 and the next is in row 9 etc. i tried
your formula and it returned nothing.

"Andrea Jones" wrote:

Assuming you are displaying your first result in row 1 and the next one in
row 7, try the following:

=IF(ROW()-(INT(ROW()/6)*6)=1,INDIRECT(CONCATENATE("Sheet2!A",(INT(ROW()/6)+1))),"")

Andrea Jones
www.allaboutoffice.co.uk

"Picman" wrote:

I have a column of cells that import there values from another worksheet with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference data on
"sheet 2" is in a column with no spaces. If it were only a couple of entries
i would do it manualy but there are about 4500 of them. i was hoping to use
an "indirect" function but i don't seem to be able to copy the formula and
have the cell reference progress down the list, it copies the same cell
reference into all copied cells.

Try this:

=OFFSET(Sheet2!$A$3,(ROWS($1:1)-1)*6,)


"Picman" wrote:

actually the first cell is in row 3 and the next is in row 9 etc. i tried
your formula and it returned nothing.

"Andrea Jones" wrote:

Assuming you are displaying your first result in row 1 and the next one in
row 7, try the following:

=IF(ROW()-(INT(ROW()/6)*6)=1,INDIRECT(CONCATENATE("Sheet2!A",(INT(ROW()/6)+1))),"")

Andrea Jones
www.allaboutoffice.co.uk

"Picman" wrote:

I have a column of cells that import there values from another worksheet with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference data on
"sheet 2" is in a column with no spaces. If it were only a couple of entries
i would do it manualy but there are about 4500 of them. i was hoping to use
an "indirect" function but i don't seem to be able to copy the formula and
have the cell reference progress down the list, it copies the same cell
reference into all copied cells.

Neither of these worked. Maybe I'm not explaining this properly So I'll try
to clarify it. I'm building the formulas in cell A3, A9, A15, A21 etc in my
results worksheet. The data that I want to retrieve is on another worksheet
named "Prods" where the data is in cell A1 then A2 then A3 etc. I want Cell
A3 On my results worksheet to retrieve the value in A1 from the data
worksheet ("Prods") and A9 to retrieve the value in A2, and A21 to retrieve
the value in A3 etc. And I would like to include the benefits of using the
"indirect" function so that if the values change or rows are added I want
these cells to always pull the value in the cell designated. I gues if i had
to i could insert 5 blank rows between all of the data on the "Prods"
worksheet but that seems in

"Teethless mama" wrote:

Try this:

=OFFSET(Sheet2!$A$3,(ROWS($1:1)-1)*6,)


"Picman" wrote:

actually the first cell is in row 3 and the next is in row 9 etc. i tried
your formula and it returned nothing.

"Andrea Jones" wrote:

Assuming you are displaying your first result in row 1 and the next one in
row 7, try the following:

=IF(ROW()-(INT(ROW()/6)*6)=1,INDIRECT(CONCATENATE("Sheet2!A",(INT(ROW()/6)+1))),"")

Andrea Jones
www.allaboutoffice.co.uk

"Picman" wrote:

I have a column of cells that import there values from another worksheet with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference data on
"sheet 2" is in a column with no spaces. If it were only a couple of entries
i would do it manualy but there are about 4500 of them. i was hoping to use
an "indirect" function but i don't seem to be able to copy the formula and
have the cell reference progress down the list, it copies the same cell
reference into all copied cells.

=OFFSET(Sheet2!A$1,(ROW(A1)-1)/6,) and copy into your cells 6 rows apart.
--
David Biddulph

"Picman" wrote in message
...
I have a column of cells that import there values from another worksheet
with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference data on
"sheet 2" is in a column with no spaces. If it were only a couple of
entries
i would do it manualy but there are about 4500 of them. i was hoping to
use
an "indirect" function but i don't seem to be able to copy the formula and
have the cell reference progress down the list, it copies the same cell
reference into all copied cells.

Andrea's message from 15:33 yesterday does the job, subject to changing the
sheet reference to match your sheet name.

=IF(ROW()-(INT(ROW()/6)*6)=3,INDIRECT(CONCATENATE("Prods!A",(INT(ROW()/6)+1))),"")
--
David Biddulph

"Picman" wrote in message
...
Neither of these worked. Maybe I'm not explaining this properly So I'll
try
to clarify it. I'm building the formulas in cell A3, A9, A15, A21 etc in
my
results worksheet. The data that I want to retrieve is on another
worksheet
named "Prods" where the data is in cell A1 then A2 then A3 etc. I want
Cell
A3 On my results worksheet to retrieve the value in A1 from the data
worksheet ("Prods") and A9 to retrieve the value in A2, and A21 to
retrieve
the value in A3 etc. And I would like to include the benefits of using the
"indirect" function so that if the values change or rows are added I want
these cells to always pull the value in the cell designated. I gues if i
had
to i could insert 5 blank rows between all of the data on the "Prods"
worksheet but that seems in

"Teethless mama" wrote:

Try this:

=OFFSET(Sheet2!$A$3,(ROWS($1:1)-1)*6,)


"Picman" wrote:

actually the first cell is in row 3 and the next is in row 9 etc. i
tried
your formula and it returned nothing.

"Andrea Jones" wrote:

Assuming you are displaying your first result in row 1 and the next
one in
row 7, try the following:

=IF(ROW()-(INT(ROW()/6)*6)=1,INDIRECT(CONCATENATE("Sheet2!A",(INT(ROW()/6)+1))),"")

Andrea Jones
www.allaboutoffice.co.uk

"Picman" wrote:

I have a column of cells that import there values from another
worksheet with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference
data on
"sheet 2" is in a column with no spaces. If it were only a couple
of entries
i would do it manualy but there are about 4500 of them. i was
hoping to use
an "indirect" function but i don't seem to be able to copy the
formula and
have the cell reference progress down the list, it copies the same
cell
reference into all copied cells.

That worked perfectly, thank you very much

"David Biddulph" wrote:

=OFFSET(Sheet2!A$1,(ROW(A1)-1)/6,) and copy into your cells 6 rows apart.
--
David Biddulph

"Picman" wrote in message
...
I have a column of cells that import there values from another worksheet
with
simple formula;
=sheet2!a1
=sheet2!a2
=sheet2!a3, etc.

The problem is that these cells are 6 rows apart and the reference data on
"sheet 2" is in a column with no spaces. If it were only a couple of
entries
i would do it manualy but there are about 4500 of them. i was hoping to
use
an "indirect" function but i don't seem to be able to copy the formula and
have the cell reference progress down the list, it copies the same cell
reference into all copied cells.