Keeping Cell Location the Same

I made a formular that looks like this:

=IF(COUNTIF(A2:A6,A2)>1,"Duplicate","")

Now what I want to do is copy and paste it down the whole row. I want 
the last A2 (,A2) to increment with each row. e.g. The next one would 
say:

=IF(COUNTIF(A2:A6,A2)>1,"Duplicate","")

But I want A2:A6 to remain static. Isn't there a symbol I put before 
the A2:A6 to keep it from changing?

Thanks,

Brian 


0
bbagnall (3)
12/14/2005 2:14:51 AM
excel 39879 articles. 2 followers. Follow

2 Replies
393 Views

Similar Articles

[PageSpeed] 42

Brian,

=IF(COUNTIF($A$2:$A$6,A2)>1,"Duplicate","")

HTH,
Bernie
MS Excel MVP

"Brian Bagnall" <bbagnall@mts.net> wrote in message 
news:LfLnf.3528$RW7.1175@fe14.lga...
>I made a formular that looks like this:
>
> =IF(COUNTIF(A2:A6,A2)>1,"Duplicate","")
>
> Now what I want to do is copy and paste it down the whole row. I want the 
> last A2 (,A2) to increment with each row. e.g. The next one would say:
>
> =IF(COUNTIF(A2:A6,A2)>1,"Duplicate","")
>
> But I want A2:A6 to remain static. Isn't there a symbol I put before the 
> A2:A6 to keep it from changing?
>
> Thanks,
>
> Brian
> 


0
Bernie
12/14/2005 2:42:29 AM
I don't see any difference between your 2 formulas.

However, what you want is the dollar sign ($).
That changes relative references to absolute references,
Which means the absolutes *don't* change when copied.

Place it before a column and/or row reference.
A1
$A1
$A$1
A$1

You can select a reference in the formula bar and then hit <F4> to change
it.
Each hit of <F4> will shift the references between row and column in a loop.
-- 
HTH,

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



"Brian Bagnall" <bbagnall@mts.net> wrote in message
news:LfLnf.3528$RW7.1175@fe14.lga...
> I made a formular that looks like this:
>
> =IF(COUNTIF(A2:A6,A2)>1,"Duplicate","")
>
> Now what I want to do is copy and paste it down the whole row. I want
> the last A2 (,A2) to increment with each row. e.g. The next one would
> say:
>
> =IF(COUNTIF(A2:A6,A2)>1,"Duplicate","")
>
> But I want A2:A6 to remain static. Isn't there a symbol I put before
> the A2:A6 to keep it from changing?
>
> Thanks,
>
> Brian
>
>

0
ragdyer1 (4060)
12/14/2005 2:45:00 AM
Reply:

Similar Artilces:

Locked cell reference
Is there a way to LOCK a cell reference to a particular cell? I don't mean absolute reference. I want the cell reference (example 'A1') to stay reference or locked on 'A1' even though a row or cell is added above or a column or cell is added to the left of 'A1'. Thanks! =indirect("a1") will always point at A1. AvalancheMike wrote: > > Is there a way to LOCK a cell reference to a particular cell? I don't mean > absolute reference. I want the cell reference (example 'A1') to stay > reference or locked on 'A1' even ...

Formula showing in cell #2
I have a formula that I entered in a cell but instead of the results the formula is showing in the cell. How can I get the results to show. Thanks. Hi - check that this cell is not formated as 'Text' (Format - Cells'). Change the format to 'General' and re-enter your formula - check in 'tools - Options - View' that 'Formula' is unchecked -- Regards Frank Kabel Frankfurt, Germany Denise wrote: > I have a formula that I entered in a cell but instead of the results > the formula is showing in the cell. > > How can I get the results to show....

specify non-contiguous cell range as arguments to TTEST()
Hello, I have data from two groups interleaved in the same column. For the sake of simplicity, let's imagine group A: rows 1-3,7-9; group B: rows 4-6,10-12. I want to compare groups A and B with a ttest -witout having to displace values everywhere-. However, I can not specify ranges, otherwise the ttest gets too many arguments (the coma for specifying ranges is taken as the coma that separates the different arguments. I tried using named selections (shift+click on cells of group A, then name 'groupA' at the left of the function bar, similar for group B), but it sti...

Keeping moving
In the cell D5 showing the time in the format: 9:23:43 (h:mm:ss), the time will only update when I click on the cell D5. Therefore, is there any way to keep "ss" moving without manually click on the cell? Many thanks for any one advice. Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200807/1 I suppose you could use something like AutoHotKey (http://www.autohotkey.com) to feed an F9 to Excel every second, but that seems sort of wasteful. Can you tell us why you need a live clock in an Excel spreadsheet? There are lots of cl...

Finding value in the last cell in column A
Hi I have this expanding Excel-database, and on all occasions I want to use the latest (max rowindex) value of the datainput in column A. I'm certain this is a-piece-of-cake, but what kind of formula is to be used? Kindly regards Snoopy Try either: =LOOKUP(99^99,A:A) will show the last number in column A =LOOKUP(2,1/(A1:A65535<>""),A1:A65535) will show numbers or text. Hope this helps "Snoopy" wrote: > Hi > I have this expanding Excel-database, and on all occasions I want to > use the latest (max rowindex) value of the datainput in column A. > I...

How many lines in one cell?
After I get to about 150 row height the text start moving down and I can't put any more inside the cell. How can I put more text in the cell without doing comments? I have Office 2002 Excel specifications allow only 1024 characters to be displayed in, or printed in, a cell. You can work around that limit by including a LF character (ALT-ENTER) at least one every 1024 characters. In article <yP0Yj.67979$3i3.40448@newsfe14.phx>, "Richard" <aimill@cox.net> wrote: > After I get to about 150 row height the text start moving down and I can't > put any...

Outlook XP Std Keeps prompting for a password
I have a remote user whos home computer is not on our domain. They connect to the domain through a vpn tunnel. I just changed their domain password, now whenever they try to use Outlook from their home computer they get prompted with a username, password, and domain box. But no matter what I put in there it wont connect. I tried creating a new profile and I can connect but it continually prompts me for credentials. I can browse shares and connect to everything else on the domain with their computer. I am at a loss and really need this resolved please. I have read and done the following links: ...

count number of cells
What is the formula to count the number of cells that start with a particular character? What I really want to do is count how the number of cells in a column that start with a through e. I have tried several things but can't quite come up with the magic formula. thanks for your help. =COUNTIF(A1:A20,"A*")+COUNTIF(A1:A20,"B*")+COUNTIF(A1:A20,"C*")+COUNTIF(A1:A20,"D*")+COUNTIF(A1:A20,"E*") -- David Biddulph "tagout" <tagout@discussions.microsoft.com> wrote in message news:2B624B5C-E73A-4481-A3E7-C9207F72DAB...

Outlook keeps reloading all messages
I have just installed Outlook 2002 on a new computer Every time I hit the send/receive button, Outlook reloads all the messages that are on the server, more than 1200 at this time How do I fix that ? Thanks to all in advance -- beaureg beaureg wrote: > I have just installed Outlook 2002 on a new computer On a new computer running WHAT operating system? > Every time I hit the send/receive button, Outlook reloads all the > messages that are on the server, more than 1200 at this time > How do I fix that ? Did you alter the default behavior for the POP e-mail account defined...

Error in sum function when using merged cells
Help! I recently discovered an error in my shipping worksheet. sometimes need to merge certain cells when I am combining items fro different purchase orders into the same shipment. However, when totaled the column (SUM) containing the merged cells, it delivers wrong answer. Specifically column F and column H that contain the merged cells don' add up correctly. Column F is +1 more than it should be. Column H i +125 more than it should be. I have been using this feature for about 6 months, and this is th first time it did this. Any ideas why the error +----------------------...

How do I keep the personal.xls from opening?
For some reason, when I created a macro today, the personal.xls worksheet keeps opening at the time I open Excel. I want the macros to be available for all worksheets and not just the one I'm working on. This hasn't been an issue before. What happened? This is where macros are kept when they need to be used in all files However, generally Personal.xls is opened in hidden mode (Widows | Hide) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "dbritt" <dbritt@discussions.microsoft.com> wrote in message news:A3F355C9-06C7-4E40-8F47-9AE358...

Formula cells
I have modified Gord Dibben's macro from a few days ago to color the cells in the workbook that contain formulas. However, after I do that I want to return the cells to the original color. I toyed with the idea of creating an elaborate array to capture the original color and then rerun the macro using that color. However, in one VBA book I have it talks about the computing overhead the Redim takes and suggests using a Collection (but doesn't elaborate). Anybody have a clue on how best to do this? Glen Glen, Since you didn't post Gord's macro, I used my own to show how t...

How to change Exchange store location by code ?
Hi, I want to change the location of the MailBox store database location and the Public folder store database location. I try to use LDAP without success. Any ideas ? Thanks, Christophe. Hi Christophe Try this 257184 XADM: How to Move Exchange Databases and Logs in Exchange 2000 Server http://support.microsoft.com/?id=257184 Hope this helps -- Melissa Travers, MCSE Microsoft Exchange Support Please do not send email directly to this alias. This alias is for newsgroup purposes only. This posting is provided "AS IS" with no warranties, and confers no rights. "Chris...

Outgoing email doesn't send and Entourage Continues to keep trying to send
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) I have two emails that Entourage keeps trying to send but can't. (It returns and error message each time) So at the bottom where it says &quot;messages remaining to be sent&quot; my screen says &quot;2&quot;. I'd like to know how to delete this mail as it is slowing me up when I try to send new mail. The problem is I do not know how to delete, b/c the mail doesn't show up in any of my folders such as &quot;drafts&quot;, etc. The error messages say &quot;connection to the sever failed or was droppe...

Help with cell referencing.
I have an Excel file which contains multiple worksheets. Worksheet 1 has data that is imported from an external source. The data is imported in rows. For example row 1 is the data name (header). Row 2 is data set 1, row 3 is data set 2, row 4 is data set 3......etc. etc. Worksheet 2 just has the data transposed into columns. So the header is column A, column B is data set 1, colum C, is data set 3......etc. etc. Worksheets 3, 4 ,5....and so on are the presentation sheets (printed reports will be generated from these sheets. Worksheet 3 only requires data from column B of Worksheet 2. ...

OWA keeps prompting credential window
I am currently using MS Exchange 2000 SP3. IE version 6.0.2800.1106 Office 2000 (9.0.6926 SP3) It started quite some time back. Everytime I try to load something on my OWA it prompts me to key in my username and password. I've check Article-317901. It didn't help much since I am using the updated versions. It seems to me that most of my colleague in the organisation has this problem. Those back in the head office does not. (they have a faster link but how does a slower link create this problem?) ...

Use a cell value as part of a formula
Hi All, I'm trying to use a cell value as part of a vlookup function. The idea is to keep changing the lookup query based on a the contents of a specific cell This is the forumla I'm currently using VLOOKUP($A2,'C:\Matrix0203\07-Feb03\[025-MainPub-ET-1801.XLS]Sheet1'!$A$1:$M$200,2,FALSE) I need a way to replace "025-MainPub-ET-1801.XLS" in the formula based on another cell, where this will be generated (or manually entered). Right now I'm doing a select all, find and replace to achieve this. Any help will be appreciated... Chirag Hi Chirag, You could use ...

find and replace formulas keeping relative addresses
I have some spreadsheets with incorrect formulas. I'd like to replace the existing formulas with correct ones. I can't do a fill down because there are merged cells in the column. Is there a way to do a find and replace to change the formulas? I did do a search in this NG for this answer, and a found a thread called "find and replace formulas but keep relative addressing". I tried that solution, which was very brief, and got no results. Does anyone know of a way to do this? I should have said I'm using XL2000 SR1. Sorry! I went back and played with more variations on ...

Linking cell to another excel file
How do you link the data from one cell on file1.xls to another cell on file2.xls? I need a cell to populate data from a 2nd excel file without having to open the 2nd file. Is this possible? How would I do this? Thanks, Jasper Have both files open, and in File1.xls Sheet1 Cell A1 type: = then go to WINDOW->File2.xls (this will switch to the second workbook) and select Sheet1 Cell A1 in File2.xls and hit enter. Fairly simple. That will link File1.xls Sheet1 Cell A1 to File2.xls Sheet1 cell A1 ryanb. "Jasper Recto" <jrecto99@yahoo.com> wrote in message news:#HReNk$...

Keep Lines Together; Keep with Next
I'm working in Vista. When I select lines and click on "Keep Lines Together," the lines do not stay together. They stay together if I also click "Keep with Next," but I'm working on a booklet in which the pages are 1/4 of a letter-sized page, and a lot of space (4 lines) is left at the bottom and wasted if I also click "Keep with Next." Why won't "Keep Lines Together" keep the lines together? The project is a small, bound country club booklet, with names, addresses, phones, etc. Each entry might be 2, 3, or 4 lines. Any h...

Cell format 12-16-09
I have some cells that have somehow become like they are frozen. They are not protected but when right click or select 'Format'>'Cell' nothing happens and I can't format those certain cells. Can someone tell me how to fix this please? If the contents of the cell are text, then the various number options under Format Cells will have no effect. Use =ISTEXT(cellref) and =ISNUMBER(cellref) to tell you whether the contents are text or number. Is it just the number formats that you can't change? Can you change colour, for example? -- David Biddulph ...

go to first blank cell
Hi, I just want to go to first blank cell (eg A3) in a specified column. If I used Selection(x1Down), then it goes to A2, not A3. How to write macro to go to A3? Column A A1 3 A2 4 A3 A4 5 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Raymond? Using your exact example, the following would work Sub FirstBlank() Selection.End(xlDown).Offset(1, 0).Select End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@btinternet.com.ANDT...

why does Publisher keep shuting down?
I have a user who is using Publisher 2000. Since she doesn't use Publisher often I don't know when this changed, but now when she opens the document, it propts her to change the printer (that part is normal) but when you say ok to change printers, it just closes the file, and program. I've checked to see of there are any updates, and the system seems to be up to date. Anyone have any ideas on what might be going on? Thanks Kelvin Try this in safe mode, if the publication opens okay, there is no doubt the printer driver needs to be upgraded. Go to the manufacturer's...

Fill cells from non-adjacent cells
I am populating a worksheet with values from another worksheet. For example the cells D6:Y6 on Sheet1, need results from various cells on Sheet2. The problem is that the cells on Sheet2 are not contiguous, that is they will be (for example) Sheet2!E15 (to populate Sheet1!D6), Sheet2!J15 (to populate Sheet1!E6), Sheet2!O15 (to populate Sheet1!F6) and so on. (Note: There is data in the cells between cell E15 and J15 and so on) Is there a simpler way to 'auto-fill' these cells, or is there some quicker way of accomplishing this rather than by cut and paste, or swapping between work...

Cell format switches to TEXT
Whenever I edit a cell it changes format to TEXT. Then I have to set the cell format back to GENERAL, type a CR in the formula bar, and then I see my calculations. This is only happening in one workbook so there's something fishy about it's properties. Is there a default cell format type? ~Bob M. My bet is that your formula points at a cell that's formatted as text. If you put today's date in A1 (ctrl-semicolon will do it) then put =a1 in B1 You'll see that B1 inherited A1's format. Same type of thing happens with Text cells. I don't think you can do any...