Absolute References in cell formula

You need to use a formula like

=IF(x=y,INDEX($B$3:$IQ$700,INT((ROW()-ROW($B$3))/250)+1,MOD(ROW()-ROW($B$3),
250)+1),"")

Change the $B$3:$IQ$700 to your actual table, and all the other references
to $B$3 to the upper left cell of your table. Also, the 250 has to be the
exact column count of your table.

HTH,
Bernie
MS Excel MVP


"ah666" <[email protected]> wrote in message
news:[email protected]...
> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and
takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells
of
> the column. if the criteria is not met then the cells are "". Continue
this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can
freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy
the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

How about

INDIRECT("b"&ROW())


"ah666" wrote:

> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells of
> the column. if the criteria is not met then the cells are "". Continue this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

That was posted before it should have been

How about:

=IF(x=y,INDIRECT("A"&ROW())-800,"")

which works if you're starting in row 801.


"Duke Carey" wrote:

> How about
>
> INDIRECT("b"&ROW())
>
>
> "ah666" wrote:
>
> > I understand the basic principles of absolute references when copying an
> > dpasting formula's.
> >
> > However I have a set of data that is contained in around 700 rows and takes
> > up most of the 256 columns. What I want to do is transfer this to a single
> > column a row at a time based upon a set of criteria. For instnace if the
> > criteria is met then include the first row of data in the first 250 cells of
> > the column. if the criteria is not met then the cells are "". Continue this
> > down for each of the 700 rows
> >
> > What I cant do is work out how to copy a formula down the page (I can freeze
> > the criteria no problem) but how do I say if x=y,a1,"" next cell down
> > x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> > formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> > b to c to d etc.
> >
> > Any ideas ? I hope I have explained this OK

Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

Try this also, since Index is not volatile compared to Offset:

=IF($B$2=$B$3,INDEX($1:$1,,ROW(A1)),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

That was posted before it should have been

How about:

=IF(x=y,INDIRECT("A"&ROW())-800,"")

which works if you're starting in row 801.


"Duke Carey" wrote:

> How about
>
> INDIRECT("b"&ROW())
>
>
> "ah666" wrote:
>
> > I understand the basic principles of absolute references when copying an
> > dpasting formula's.
> >
> > However I have a set of data that is contained in around 700 rows and takes
> > up most of the 256 columns. What I want to do is transfer this to a single
> > column a row at a time based upon a set of criteria. For instnace if the
> > criteria is met then include the first row of data in the first 250 cells of
> > the column. if the criteria is not met then the cells are "". Continue this
> > down for each of the 700 rows
> >
> > What I cant do is work out how to copy a formula down the page (I can freeze
> > the criteria no problem) but how do I say if x=y,a1,"" next cell down
> > x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> > formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> > b to c to d etc.
> >
> > Any ideas ? I hope I have explained this OK

Try this also, since Index is not volatile compared to Offset:

=IF($B$2=$B$3,INDEX($1:$1,,ROW(A1)),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

How about

INDIRECT("b"&ROW())


"ah666" wrote:

> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells of
> the column. if the criteria is not met then the cells are "". Continue this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

You need to use a formula like

=IF(x=y,INDEX($B$3:$IQ$700,INT((ROW()-ROW($B$3))/250)+1,MOD(ROW()-ROW($B$3),
250)+1),"")

Change the $B$3:$IQ$700 to your actual table, and all the other references
to $B$3 to the upper left cell of your table. Also, the 250 has to be the
exact column count of your table.

HTH,
Bernie
MS Excel MVP


"ah666" <[email protected]> wrote in message
news:[email protected]...
> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and
takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells
of
> the column. if the criteria is not met then the cells are "". Continue
this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can
freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy
the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

You need to use a formula like

=IF(x=y,INDEX($B$3:$IQ$700,INT((ROW()-ROW($B$3))/250)+1,MOD(ROW()-ROW($B$3),
250)+1),"")

Change the $B$3:$IQ$700 to your actual table, and all the other references
to $B$3 to the upper left cell of your table. Also, the 250 has to be the
exact column count of your table.

HTH,
Bernie
MS Excel MVP


"ah666" <[email protected]> wrote in message
news:[email protected]...
> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and
takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells
of
> the column. if the criteria is not met then the cells are "". Continue
this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can
freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy
the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

How about

INDIRECT("b"&ROW())


"ah666" wrote:

> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells of
> the column. if the criteria is not met then the cells are "". Continue this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

That was posted before it should have been

How about:

=IF(x=y,INDIRECT("A"&ROW())-800,"")

which works if you're starting in row 801.


"Duke Carey" wrote:

> How about
>
> INDIRECT("b"&ROW())
>
>
> "ah666" wrote:
>
> > I understand the basic principles of absolute references when copying an
> > dpasting formula's.
> >
> > However I have a set of data that is contained in around 700 rows and takes
> > up most of the 256 columns. What I want to do is transfer this to a single
> > column a row at a time based upon a set of criteria. For instnace if the
> > criteria is met then include the first row of data in the first 250 cells of
> > the column. if the criteria is not met then the cells are "". Continue this
> > down for each of the 700 rows
> >
> > What I cant do is work out how to copy a formula down the page (I can freeze
> > the criteria no problem) but how do I say if x=y,a1,"" next cell down
> > x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> > formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> > b to c to d etc.
> >
> > Any ideas ? I hope I have explained this OK

Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

Try this also, since Index is not volatile compared to Offset:

=IF($B$2=$B$3,INDEX($1:$1,,ROW(A1)),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

Try this also, since Index is not volatile compared to Offset:

=IF($B$2=$B$3,INDEX($1:$1,,ROW(A1)),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

That was posted before it should have been

How about:

=IF(x=y,INDIRECT("A"&ROW())-800,"")

which works if you're starting in row 801.


"Duke Carey" wrote:

> How about
>
> INDIRECT("b"&ROW())
>
>
> "ah666" wrote:
>
> > I understand the basic principles of absolute references when copying an
> > dpasting formula's.
> >
> > However I have a set of data that is contained in around 700 rows and takes
> > up most of the 256 columns. What I want to do is transfer this to a single
> > column a row at a time based upon a set of criteria. For instnace if the
> > criteria is met then include the first row of data in the first 250 cells of
> > the column. if the criteria is not met then the cells are "". Continue this
> > down for each of the 700 rows
> >
> > What I cant do is work out how to copy a formula down the page (I can freeze
> > the criteria no problem) but how do I say if x=y,a1,"" next cell down
> > x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> > formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> > b to c to d etc.
> >
> > Any ideas ? I hope I have explained this OK

I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
b to c to d etc.

Any ideas ? I hope I have explained this OK

How about

INDIRECT("b"&ROW())


"ah666" wrote:

> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells of
> the column. if the criteria is not met then the cells are "". Continue this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

You need to use a formula like

=IF(x=y,INDEX($B$3:$IQ$700,INT((ROW()-ROW($B$3))/250)+1,MOD(ROW()-ROW($B$3),
250)+1),"")

Change the $B$3:$IQ$700 to your actual table, and all the other references
to $B$3 to the upper left cell of your table. Also, the 250 has to be the
exact column count of your table.

HTH,
Bernie
MS Excel MVP


"ah666" <[email protected]> wrote in message
news:[email protected]...
> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and
takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells
of
> the column. if the criteria is not met then the cells are "". Continue
this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can
freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy
the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

You need to use a formula like

=IF(x=y,INDEX($B$3:$IQ$700,INT((ROW()-ROW($B$3))/250)+1,MOD(ROW()-ROW($B$3),
250)+1),"")

Change the $B$3:$IQ$700 to your actual table, and all the other references
to $B$3 to the upper left cell of your table. Also, the 250 has to be the
exact column count of your table.

HTH,
Bernie
MS Excel MVP


"ah666" <[email protected]> wrote in message
news:[email protected]...
> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and
takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells
of
> the column. if the criteria is not met then the cells are "". Continue
this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can
freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy
the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

How about

INDIRECT("b"&ROW())


"ah666" wrote:

> I understand the basic principles of absolute references when copying an
> dpasting formula's.
>
> However I have a set of data that is contained in around 700 rows and takes
> up most of the 256 columns. What I want to do is transfer this to a single
> column a row at a time based upon a set of criteria. For instnace if the
> criteria is met then include the first row of data in the first 250 cells of
> the column. if the criteria is not met then the cells are "". Continue this
> down for each of the 700 rows
>
> What I cant do is work out how to copy a formula down the page (I can freeze
> the criteria no problem) but how do I say if x=y,a1,"" next cell down
> x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> b to c to d etc.
>
> Any ideas ? I hope I have explained this OK

That was posted before it should have been

How about:

=IF(x=y,INDIRECT("A"&ROW())-800,"")

which works if you're starting in row 801.


"Duke Carey" wrote:

> How about
>
> INDIRECT("b"&ROW())
>
>
> "ah666" wrote:
>
> > I understand the basic principles of absolute references when copying an
> > dpasting formula's.
> >
> > However I have a set of data that is contained in around 700 rows and takes
> > up most of the 256 columns. What I want to do is transfer this to a single
> > column a row at a time based upon a set of criteria. For instnace if the
> > criteria is met then include the first row of data in the first 250 cells of
> > the column. if the criteria is not met then the cells are "". Continue this
> > down for each of the 700 rows
> >
> > What I cant do is work out how to copy a formula down the page (I can freeze
> > the criteria no problem) but how do I say if x=y,a1,"" next cell down
> > x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
> > formula down the page the numebr increases a2, a3 a4 etc. I cant get the a to
> > b to c to d etc.
> >
> > Any ideas ? I hope I have explained this OK

Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK

Try this also, since Index is not volatile compared to Offset:

=IF($B$2=$B$3,INDEX($1:$1,,ROW(A1)),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
Try this:

=IF($B$2=$B$3,OFFSET($A$1,,ROW(A1)-1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ah666" <[email protected]> wrote in message
news:[email protected]...
I understand the basic principles of absolute references when copying an
dpasting formula's.

However I have a set of data that is contained in around 700 rows and takes
up most of the 256 columns. What I want to do is transfer this to a single
column a row at a time based upon a set of criteria. For instnace if the
criteria is met then include the first row of data in the first 250 cells of
the column. if the criteria is not met then the cells are "". Continue this
down for each of the 700 rows

What I cant do is work out how to copy a formula down the page (I can freeze
the criteria no problem) but how do I say if x=y,a1,"" next cell down
x=y,b1,"", next cell down x=y,c1,"". Every time i try to replicate/copy the
formula down the page the numebr increases a2, a3 a4 etc. I cant get the a
to
b to c to d etc.

Any ideas ? I hope I have explained this OK