Formula corresponding to row it is in?

[REBANEEDSTOKNOW]

Ok, I'm trying to have a function apply to an entire set of information.
What I need is for the function to adapt to the row it is in... like, when I
enter this...
=COUNTIF($A1:$A7999,A1)>1
....in D1, it references A1. But then in D2, I need it to switch to...
=COUNTIF($A1:$A7999,A2)>1
....where it references A2

I guess what I'm asking is how can I automate it so I don't have to go in by
hand for 8000 rows and change the information. And also, how can I apply the
formula to the whole D column, without having to copy and paste it into each
box. Thank you!

 

[Fred Smith]

It will. Just copy it down. But change the formula to:
=COUNTIF($A$1:$A$7999,A1)>1
Then copy.

The best way to copy formulas is to grab the fill handle (the small black
box on the bottom right of the cell). Then drag it down the column. Excel
will adjust the formulas as it goes.

One trick to fill an entire column is to double click on the fill handle.
This will fill down the the last cell in the adjacent column (C). It works
great if you have good database organization.

If this doesn't work for you, post back, with specifics of how your data is
organized. Someone will come up with a shortcut.

Regards,
Fred

 

[Bill Kuunders]

=COUNTIF(A$1:A$7999,A1)>1

note the dollar signs moved
now you can extend this down the column using mouse drag method
point to the right hand bottom corner of D1
the mouse pointer will change to a "+" sign
drag down.....
You can do a copy paste to the whole column but then you do have 65536 rows.

are you trying to find duplicates?
be careful this formula will be true for both of them.

 

[Max]

One other way ...
1. Type the range in the namebox, eg: D18000,
press ENTER to select the range
2. Paste the top cell's formula, ie that for D1, into the formula bar
3. Press CTRL+ENTER to fill down the entire range at one go
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik