Validation Warning for repeating a number in a column
Closed
barewirewalker
Posts
3
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 24, 2013

Jun 18, 2013 at 10:12 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022  Jun 27, 2013 at 11:37 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022  Jun 27, 2013 at 11:37 AM
Related:
 Validation Warning for repeating a number in a column
 Windows10upgrade9252.exe is not a valid win32  Guide
 Display two columns in data validation list but return only one  Guide
 Google maps warning symbols  Guide
 Warning: potential security risk ahead firefox disable  Guide
 How to convert column to row in notepad++ ✓  Excel Forum
7 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 18, 2013 at 10:57 AM
Jun 18, 2013 at 10:57 AM
Hi Barewirewalker,
You could use an array formula.
Let's say your references are in column A, then your formula would look like:
=SUM(IF($A$2:$A$100=A2;1;0))
Array formula's need to be confirmed by hitting Ctrl+Shift+Enter.
You can now drag the formula down as far as needed.
Also adjust the range A2:A100 to the range you might be using.
The formula will count the number of times a reference is used, so you can also apply conditional format to cells bigger then 1.
Please let me know if something is unclear.
Best regards,
Trowa
You could use an array formula.
Let's say your references are in column A, then your formula would look like:
=SUM(IF($A$2:$A$100=A2;1;0))
Array formula's need to be confirmed by hitting Ctrl+Shift+Enter.
You can now drag the formula down as far as needed.
Also adjust the range A2:A100 to the range you might be using.
The formula will count the number of times a reference is used, so you can also apply conditional format to cells bigger then 1.
Please let me know if something is unclear.
Best regards,
Trowa
barewirewalker
Posts
3
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 24, 2013
Jun 18, 2013 at 12:24 PM
Jun 18, 2013 at 12:24 PM
Thanks, Trowa, for that prompt reply, Excel tells me I am using an incorrect formula,
Perhaps I should have mentioned that I am using Excel 2003.
but I shall continue to persevere with it, and check that I am in the correct cells.
Perhaps I should have mentioned that I am using Excel 2003.
but I shall continue to persevere with it, and check that I am in the correct cells.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 20, 2013 at 10:26 AM
Jun 20, 2013 at 10:26 AM
Hi Barewirewalker,
I'm also using Excel 2003, so the formula should work.
When I take a second look at the formula I notice that I forgot to change the ;'s into ,'s.
So the formula should be:
=SUM(IF($A$2:$A$100=A2,1,0))
Don't forget to confirm using Ctrl+Shift+Enter. If done correctly the formula should be enclosed by curly brackets.
Best regards,
Trowa
I'm also using Excel 2003, so the formula should work.
When I take a second look at the formula I notice that I forgot to change the ;'s into ,'s.
So the formula should be:
=SUM(IF($A$2:$A$100=A2,1,0))
Don't forget to confirm using Ctrl+Shift+Enter. If done correctly the formula should be enclosed by curly brackets.
Best regards,
Trowa
Thanks again for your interest and help.
I have been able to enter the formula into the Data>Validation>custom>box and I have filled in the appropriate messages for a warning, but I am not getting a reaction/warning when I enter a same reference eg. SJ4544, and SJ4544.
I am not very savvy with Excel, as I have not had to use it very much so I may well be making elementary mistakes. I will continue to persevere.
By "Don't forget to confirm using Ctrl+Shift+Enter" I presume this is alternative to clicking on enter to activate the validation, though I have tried Ctrl+Shift+Enter as I type in the incorrect Grid Reference.
I have been able to enter the formula into the Data>Validation>custom>box and I have filled in the appropriate messages for a warning, but I am not getting a reaction/warning when I enter a same reference eg. SJ4544, and SJ4544.
I am not very savvy with Excel, as I have not had to use it very much so I may well be making elementary mistakes. I will continue to persevere.
By "Don't forget to confirm using Ctrl+Shift+Enter" I presume this is alternative to clicking on enter to activate the validation, though I have tried Ctrl+Shift+Enter as I type in the incorrect Grid Reference.
Didn't find the answer you are looking for?
Ask a question
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 24, 2013 at 10:40 AM
Jun 24, 2013 at 10:40 AM
Hi Barewirewalker,
Data Validation doesn't accept array formula's.
The formula I have given you needs to be put in a column.
The result will be the number of times a reference is used.
That is why I advised you to apply conditional formatting to clearly see if a reference is used already.
The column used can be hidden if desired.
Will this work for you or do you prefer a macro code.
Best regards,
Trowa
Data Validation doesn't accept array formula's.
The formula I have given you needs to be put in a column.
The result will be the number of times a reference is used.
That is why I advised you to apply conditional formatting to clearly see if a reference is used already.
The column used can be hidden if desired.
Will this work for you or do you prefer a macro code.
Best regards,
Trowa
barewirewalker
Posts
3
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 24, 2013
Jun 24, 2013 at 01:59 PM
Jun 24, 2013 at 01:59 PM
Hi Trowa,
That will explain why, even having managed to enter the formula, I have had no success. It returns the warning for any no. entered.
This also happens with a formula I found here;
https://thesmartmethod.com/search/excel2010/formulas.html
Excel 2010 Expert Skills Lesson 214
But this formula/function has the same effect once entered it does not allow any number to entered. So I presume Excel 2003 does not handle this as well.
Your suggestion of a column enumerating the no. of entries so that there is a visual check makes sense.
Not sure I understand macros yet. I still have an enormous amount of data to process and am looking at .csv files to enter this into the spread sheet, so I will be looking for any improvements to help keep out errors.
As the serial numbers are grid references, I have decided to have a map of the references on a separate sheet in the same Excel document.
Again thanks for your help, it has helped me progress in understanding, which would have been difficult without
That will explain why, even having managed to enter the formula, I have had no success. It returns the warning for any no. entered.
This also happens with a formula I found here;
https://thesmartmethod.com/search/excel2010/formulas.html
Excel 2010 Expert Skills Lesson 214
But this formula/function has the same effect once entered it does not allow any number to entered. So I presume Excel 2003 does not handle this as well.
Your suggestion of a column enumerating the no. of entries so that there is a visual check makes sense.
Not sure I understand macros yet. I still have an enormous amount of data to process and am looking at .csv files to enter this into the spread sheet, so I will be looking for any improvements to help keep out errors.
As the serial numbers are grid references, I have decided to have a map of the references on a separate sheet in the same Excel document.
Again thanks for your help, it has helped me progress in understanding, which would have been difficult without
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 27, 2013 at 11:37 AM
Jun 27, 2013 at 11:37 AM
Hi Barewirewalker,
Not sure if you found a workaround or not but the following file contains exactly what you want, right?:
http://www.ge.tt/8b7fFPk/v/0
Also it is nice to understand macro's but it is not necessary for them to work.
Best regards,
Trowa
Not sure if you found a workaround or not but the following file contains exactly what you want, right?:
http://www.ge.tt/8b7fFPk/v/0
Also it is nice to understand macro's but it is not necessary for them to work.
Best regards,
Trowa