Copy Indirect down a column

P

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

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
 
P

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

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top