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.
Similar Threads
