How to be noticed/to avoid equal values in a colum
Hi! I am working on a excel sheet that has a long colum with string values. I don't want the colum to contain any equal values. When I write new string values to the colum sometimes I am allerted if the value already excist (as I start writing the complete string appear with what is left marked in black). This is good because then I know if the value already excist in the colum and I don't retype it. However, I am not always allerted and then I end up writing the same value several times. Does anyone know how I can deal with this problem as I dont want the colun to contain any equal values? Thank you for all help. Irene:confused: -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
How to be noticed/to avoid equal values in a colum
See
http://cpearson.com/excel/NoDupEntry.htm In article , Irene wrote: Hi! I am working on a excel sheet that has a long colum with string values. I don't want the colum to contain any equal values. When I write new string values to the colum sometimes I am allerted if the value already excist (as I start writing the complete string appear with what is left marked in black). This is good because then I know if the value already excist in the colum and I don't retype it. However, I am not always allerted and then I end up writing the same value several times. Does anyone know how I can deal with this problem as I dont want the colun to contain any equal values? Thank you for all help. Irene:confused: |
How to be noticed/to avoid equal values in a colum
I can't get this to work. I follow the given instructions but as I press the Error Alert tab i get a message saying that my formula contain an error. My list is currently in the range of B2:B1270 and each cell contains a string value. In the Data Validation box I choose Custom and type the formula: =COUNTIF($B$2:$B$1270,B2)=1 Anyone know why??? Irene(almost desperate :) -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
How to be noticed/to avoid equal values in a colum
=COUNTIF($B$2:$B$1270,B2)1 I would conditionally format the cell using a formula, so in cell b2 use the above. then choose a colour say to trun it red. If you type a duplicate the cell will turn red and you should notice it! Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
How to be noticed/to avoid equal values in a colum
I got the same error message again. I have never done formatting in excel before so as a new beginner I suspect I make some elementary mistake. I have tried to do the formatting both with the Validation and through the Conditional Formatting but maybe I am missing out on something else. Is there something else I must do before the formatting???? I have tried to highlight the cells in the range, highlight all cells in the colum and no highlight of the cells before the formatting but it doesnt seems to make a difference. Irene -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
How to be noticed/to avoid equal values in a colum
Just select the first cell b2 in this instance. Sometimes excel puts the formula in " " if this is the case they need to be removed and the formula need to start with a = sign once it works it can be copied down to the other cells with copy then past special formats Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
How to be noticed/to avoid equal values in a colum
Thank you so much for your help, I really appreshiate it :) I don't know what I do wrong but I still can't get it to work. I will try to detailed explained exactely what I am doing, maybe that will help. This is what I have, A list with colum A and B with data ranging from row 2-1270, so A1:B1270 As I make more entrance to the list (in colum *B*)I want to be allerted if the value already excist. I did the following: Put the marker on cell B2. Went to Conditional Formatting Chose Formula Is Typed in formula: =COUNTIF($B$2:$B$1270,B2)1 Chose to put the result in red color. When I press OK the little (annoying) paper clip allert me that the formula contains an error. And this part of the fomula becomes highlighted: $B$1270,B2 -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
How to be noticed/to avoid equal values in a colum
I just found out what the problem was The reason for why I couldn't get any of the formulas to work is because I am using the Norwegian version of Excel and apparently I need to use a semicolon(;) instead of the comma(,). Glad to have figured this out:) -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
All times are GMT +1. The time now is 02:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com