Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula corresponding to row it is in?
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula corresponding to row it is in?
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 "REBANEEDSTOKNOW" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula corresponding to row it is in?
=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. -- Greetings from New Zealand "REBANEEDSTOKNOW" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula corresponding to row it is in?
One other way ...
1. Type the range in the namebox, eg: D1:D8000, 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 --- "REBANEEDSTOKNOW" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|