Copy Indirect down a column

[Picman]

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 Jones]

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]

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]

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

 

[Teethless mama]

Try this:

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

 

[Picman]

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

 

[David Biddulph]

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

 

[David Biddulph]

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))),"")

 

[Picman]

That worked perfectly, thank you very much

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