Validation Warning for repeating a number in a column

Closed
Report
Posts
3
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 24, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Hello,

I am using a spreadsheet that compares properties, within different grid squares. The column for grid references has numbers prefixed with two letters,eg. SV1243.

Can I use a formula to warn me if I have already entered a reference, the same as being typed in and what would it be?

Regards

7 replies

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0
Posts
3
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 24, 2013

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.
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0

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.
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0
Posts
3
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 24, 2013

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 2-14

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
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0