Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 17th 05, 02:50 PM
ah666
 
Posts: n/a
Default Absolute References in cell formula

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

  #2   Report Post  
Old June 17th 05, 03:14 PM
Bernie Deitrick
 
Posts: n/a
Default

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" wrote in message
...
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



  #3   Report Post  
Old June 17th 05, 03:20 PM
Duke Carey
 
Posts: n/a
Default

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

  #4   Report Post  
Old June 17th 05, 03:27 PM
Duke Carey
 
Posts: n/a
Default

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

  #5   Report Post  
Old June 17th 05, 03:30 PM
RagDyeR
 
Posts: n/a
Default

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" wrote in message
...
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




  #6   Report Post  
Old June 17th 05, 03:40 PM
RagDyeR
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using contents of a cell in a formula Mike Excel Discussion (Misc queries) 4 June 9th 05 03:10 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Lock or Unlock cell references in a formula for auto fill purposes David P. Excel Discussion (Misc queries) 2 June 6th 05 11:18 PM
Percent and Rank formula in one cell T.R. Excel Discussion (Misc queries) 1 March 10th 05 05:05 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 04:12 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017