|
AVOIDING DUPLICATES IN A RANGE OF CELLS
Hi Everyone,
I am very new to Excel and I need to enter a series of figures into a range of cells, say 1 to 400. How do I ensure that: a) only figures are accepted, and must be between 1 & 400 b) the figures are not duplicated? Thanks for your assistance. -- Glint |
AVOIDING DUPLICATES IN A RANGE OF CELLS
Use Data Validation.
Select all your cells, let's assume it is H1:Hn DataValidation, change allow type to Custom and add a formula of =AND(H11,H1<=400,COUNTIF($H$1:H1,H1)=1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Hi Everyone, I am very new to Excel and I need to enter a series of figures into a range of cells, say 1 to 400. How do I ensure that: a) only figures are accepted, and must be between 1 & 400 b) the figures are not duplicated? Thanks for your assistance. -- Glint |
AVOIDING DUPLICATES IN A RANGE OF CELLS
Thanx Bob, but can you spoon-feed me a little? It is so bad I even have
difficulty entring the formula. The range is B2:Z26. I need to enter 1 to 225 without duplicating any number. Nulls are allowed. How do I do it? -- Glint "Bob Phillips" wrote: Use Data Validation. Select all your cells, let's assume it is H1:Hn DataValidation, change allow type to Custom and add a formula of =AND(H11,H1<=400,COUNTIF($H$1:H1,H1)=1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Hi Everyone, I am very new to Excel and I need to enter a series of figures into a range of cells, say 1 to 400. How do I ensure that: a) only figures are accepted, and must be between 1 & 400 b) the figures are not duplicated? Thanks for your assistance. -- Glint |
AVOIDING DUPLICATES IN A RANGE OF CELLS
Hi Glint,
Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
AVOIDING DUPLICATES IN A RANGE OF CELLS
Thanx Ken. I did not realize at first that the formula only works in a blank
worksheet. I had problems with one that had data. -- Glint "Ken Johnson" wrote: Hi Glint, Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
AVOIDING DUPLICATES IN A RANGE OF CELLS
What do you mean that it only works in a blank worksheet? There is no point
if the workbook is blank. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Ken. I did not realize at first that the formula only works in a blank worksheet. I had problems with one that had data. -- Glint "Ken Johnson" wrote: Hi Glint, Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
AVOIDING DUPLICATES IN A RANGE OF CELLS
Bob, what I found was that no figures were accepted if there was data in any
cell in the range BEFORE I added the formula. When I tried the formula in another area of the worksheet that had no data whatsoever, the formula worked fine. But if any cell in the range had data, the range locked up, not allowing editing or addition of data. -- Glint "Bob Phillips" wrote: What do you mean that it only works in a blank worksheet? There is no point if the workbook is blank. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Ken. I did not realize at first that the formula only works in a blank worksheet. I had problems with one that had data. -- Glint "Ken Johnson" wrote: Hi Glint, Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
AVOIDING DUPLICATES IN A RANGE OF CELLS
That's very odd, that is not how I did it. I created the test data, then the
formula, no problem. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Bob, what I found was that no figures were accepted if there was data in any cell in the range BEFORE I added the formula. When I tried the formula in another area of the worksheet that had no data whatsoever, the formula worked fine. But if any cell in the range had data, the range locked up, not allowing editing or addition of data. -- Glint "Bob Phillips" wrote: What do you mean that it only works in a blank worksheet? There is no point if the workbook is blank. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Ken. I did not realize at first that the formula only works in a blank worksheet. I had problems with one that had data. -- Glint "Ken Johnson" wrote: Hi Glint, Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
AVOIDING DUPLICATES IN A RANGE OF CELLS
Thanx Bob, I just found that the cells at the side and bottom of my range
that I want to use to sum the totals in the rows and columns are affecting data entry. When I placed the formulas for these cells "=SUM(B2:Z2)" etc, I found I could no longer update my range. Surely, I am doing something wrong. -- Glint "Bob Phillips" wrote: That's very odd, that is not how I did it. I created the test data, then the formula, no problem. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Bob, what I found was that no figures were accepted if there was data in any cell in the range BEFORE I added the formula. When I tried the formula in another area of the worksheet that had no data whatsoever, the formula worked fine. But if any cell in the range had data, the range locked up, not allowing editing or addition of data. -- Glint "Bob Phillips" wrote: What do you mean that it only works in a blank worksheet? There is no point if the workbook is blank. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Ken. I did not realize at first that the formula only works in a blank worksheet. I had problems with one that had data. -- Glint "Ken Johnson" wrote: Hi Glint, Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
AVOIDING DUPLICATES IN A RANGE OF CELLS
Sounds like it, but I cannot see what off-hand. Can you post the workbook
somewhere, such as http://cjoint.com -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Bob, I just found that the cells at the side and bottom of my range that I want to use to sum the totals in the rows and columns are affecting data entry. When I placed the formulas for these cells "=SUM(B2:Z2)" etc, I found I could no longer update my range. Surely, I am doing something wrong. -- Glint "Bob Phillips" wrote: That's very odd, that is not how I did it. I created the test data, then the formula, no problem. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Bob, what I found was that no figures were accepted if there was data in any cell in the range BEFORE I added the formula. When I tried the formula in another area of the worksheet that had no data whatsoever, the formula worked fine. But if any cell in the range had data, the range locked up, not allowing editing or addition of data. -- Glint "Bob Phillips" wrote: What do you mean that it only works in a blank worksheet? There is no point if the workbook is blank. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Ken. I did not realize at first that the formula only works in a blank worksheet. I had problems with one that had data. -- Glint "Ken Johnson" wrote: Hi Glint, Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
AVOIDING DUPLICATES IN A RANGE OF CELLS
Bob, http://cjoint.com looks like a French site to me and I am not sure my
French is good enough. But the worksheet looks like this: a) I selected B2:Z26 and used the formula "=AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1)" to ensure that only numbers from 1 to 225 are allowed, without any repeat. This works fine. But then I went on to b) I made each row on column AA sum up whatever was entered into the range: "=SUM(B2:Z2)", etc; I also made each column on row 31 add up as in "=SUM(B2:B26)" etc. I now discovered that I would get an error message to the effect that a value is not valid as a user had restricted values that could go in. This only happens after the step (b) above. What am I doing wrong? -- Glint "Bob Phillips" wrote: Sounds like it, but I cannot see what off-hand. Can you post the workbook somewhere, such as http://cjoint.com -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Bob, I just found that the cells at the side and bottom of my range that I want to use to sum the totals in the rows and columns are affecting data entry. When I placed the formulas for these cells "=SUM(B2:Z2)" etc, I found I could no longer update my range. Surely, I am doing something wrong. -- Glint "Bob Phillips" wrote: That's very odd, that is not how I did it. I created the test data, then the formula, no problem. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Bob, what I found was that no figures were accepted if there was data in any cell in the range BEFORE I added the formula. When I tried the formula in another area of the worksheet that had no data whatsoever, the formula worked fine. But if any cell in the range had data, the range locked up, not allowing editing or addition of data. -- Glint "Bob Phillips" wrote: What do you mean that it only works in a blank worksheet? There is no point if the workbook is blank. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Ken. I did not realize at first that the formula only works in a blank worksheet. I had problems with one that had data. -- Glint "Ken Johnson" wrote: Hi Glint, Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
AVOIDING DUPLICATES IN A RANGE OF CELLS
All you need to do is use the Browse button to find your file, then press
the "Créer le lien Cjoint" button, then post the link that it gives you. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Bob, http://cjoint.com looks like a French site to me and I am not sure my French is good enough. But the worksheet looks like this: a) I selected B2:Z26 and used the formula "=AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1)" to ensure that only numbers from 1 to 225 are allowed, without any repeat. This works fine. But then I went on to b) I made each row on column AA sum up whatever was entered into the range: "=SUM(B2:Z2)", etc; I also made each column on row 31 add up as in "=SUM(B2:B26)" etc. I now discovered that I would get an error message to the effect that a value is not valid as a user had restricted values that could go in. This only happens after the step (b) above. What am I doing wrong? -- Glint "Bob Phillips" wrote: Sounds like it, but I cannot see what off-hand. Can you post the workbook somewhere, such as http://cjoint.com -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Bob, I just found that the cells at the side and bottom of my range that I want to use to sum the totals in the rows and columns are affecting data entry. When I placed the formulas for these cells "=SUM(B2:Z2)" etc, I found I could no longer update my range. Surely, I am doing something wrong. -- Glint "Bob Phillips" wrote: That's very odd, that is not how I did it. I created the test data, then the formula, no problem. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Bob, what I found was that no figures were accepted if there was data in any cell in the range BEFORE I added the formula. When I tried the formula in another area of the worksheet that had no data whatsoever, the formula worked fine. But if any cell in the range had data, the range locked up, not allowing editing or addition of data. -- Glint "Bob Phillips" wrote: What do you mean that it only works in a blank worksheet? There is no point if the workbook is blank. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Glint" wrote in message ... Thanx Ken. I did not realize at first that the formula only works in a blank worksheet. I had problems with one that had data. -- Glint "Ken Johnson" wrote: Hi Glint, Looks like you've changed from 400 to 225, so the formula should be... =AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1) If so, try these steps to get it in place and working... 1. Select B2:Z26 2. Go Data|Validation... 3. On the Settings Tab of the Data Validation dialog, click in the Allow: box and select Custom. 4. Type the above formula into the Formula: box 5. If you want a message to appear when a cell in the range is selected add it to the Input Message Tab of the Data Validation dialog 6. If you want a warning Error Alert message displayed when the user tries to enter an invalid value then add it to the Error Alert tab of the Data Validation dialog. 7. Click OK. Ken Johnson |
| All times are GMT +1. The time now is 07:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com