Solved warning to pop up if you enter a repeat number

April 12, 2017 at 18:28:47
Specs: Windows 7
in column a on two worksheets i have ticket numbers if a person tries to type a ticket number that is on either worksheets a message box will pop up and say "Try another number".

See More: warning to pop up if you enter a repeat number

Report •

✔ Best Answer
April 13, 2017 at 10:20:28
I set up Sheet 1 like this:

        A          B
1       1      =SHEET2!A1
2       2      =SHEET2!A2
3      etc.

I set up Sheet 2 like this:

        A          B
1       3      =SHEET1!A1
2       4      =SHEET1!A2
3      etc.

I then selected Columns A:B on Sheet 1 and applied Mike's Data Validation suggestion as follows:

=COUNTIF(A:B,A1)=1

I then selected Columns A:B on Sheet 2 and applied Mike's Data Validation suggestion in the same manner:

=COUNTIF(A:B,A1)=1

I then hid Column B on both sheets.

Any value entered in Column A of either sheet is checked against both columns on that sheet and duplicates are prohibited.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 13, 2017 at 00:34:50
erm... I'm not sure I understand what you're asking... Can you elaborate a little more please?

Report •

#2
April 13, 2017 at 06:52:08
Your looking for Data Validation, try this:

Select the range of cells in which you want to prevent duplicates A1:A10
Ont he Data tab, select Validation.
Click the Settings tab.
From the Allow drop down box select Custom.
In the formula box enter:

=COUNTIF($A$1:$A$10,A1)=1

Click on the Error Alert tab at the top of the Data Validation box
Give it an appropriate title in the Title box, something like: "Duplicate Entry!"
In the Error Message box you can use: "Try another number".

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#3
April 13, 2017 at 08:35:05
Mike:

If I'm not mistaken, he has a list on 2 sheets and wants to prevent duplicates from being entered on either sheet. i.e. The Data validation would need to check both sheets regardless of which sheet the user entered the value on.

I don't believe that Data Validation can be used across sheets. Perhaps a Helper Column on both sheets, each set equal to the list on the other sheet, could be used for Data Validation on each sheet individually.

If not, a macro may be required.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
April 13, 2017 at 10:20:28
✔ Best Answer
I set up Sheet 1 like this:

        A          B
1       1      =SHEET2!A1
2       2      =SHEET2!A2
3      etc.

I set up Sheet 2 like this:

        A          B
1       3      =SHEET1!A1
2       4      =SHEET1!A2
3      etc.

I then selected Columns A:B on Sheet 1 and applied Mike's Data Validation suggestion as follows:

=COUNTIF(A:B,A1)=1

I then selected Columns A:B on Sheet 2 and applied Mike's Data Validation suggestion in the same manner:

=COUNTIF(A:B,A1)=1

I then hid Column B on both sheets.

Any value entered in Column A of either sheet is checked against both columns on that sheet and duplicates are prohibited.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
April 13, 2017 at 13:19:49
Sorry, missed the part about a two sheets.

DeryDad03, very nice solution :-)

MIKE

http://www.skeptic.com/


Report •

#6
April 13, 2017 at 17:05:55
Awesome solutions guys thanks for your help. I am very sorry I wasn't clear in my explanation. On sheet 1 I have tasks that are currently in progress and each task is given a ticket number. When a task is completed it is then moved to sheet 2 bcompleted tasks. So let's say task with ticket 5 is completed and cut and put to sheet 2 and someone wants to add a new task to sheet 1 and they enter ticket 5 as the number but since ticket 5 is on sheet 2 I want a pop up to tell the user try another number or perhaps suggest the next number they could use (just thought about that now). Data validation was my first try but if someone puts a row on the complete sheet by accident then puts it back to current tasks the validation gets mixed up.

I think I'm entering into macro territory now perhaps.

message edited by MattExcel


Report •

#7
April 13, 2017 at 17:26:34
When a task is completed it is then moved to sheet 2 bcompleted tasks.

Why are you moving the data?

Just add a Completed column with a date or something,
then Data Validation will work.

MIKE

http://www.skeptic.com/


Report •

#8
April 15, 2017 at 00:03:20
Well more so because the boss wants it off the list so the list doesn't get to be too long. But I may just mention he add a filter that only shows in progress tasks and he could simply change the filter if he wants to see completed. Thanks for the question Mike it's given me something to think about.

Report •

#9
April 15, 2017 at 07:32:19
Along with the Filter, have you tried Freezing the top row, your Headings, and letting the data scroll away under the top row?
That way you can see just as many rows as needed, see here:

http://www.gcflearnfree.org/excel20...

You could also do some Conditional Formatting and turn all completed jobs
a different color, so you can tell at a glance which jobs are done and which aren't.

Good luck

MIKE

http://www.skeptic.com/


Report •

#10
April 15, 2017 at 08:03:27
There is also a method for creating a Drop Down list from a set of values where a value that has already been chosen is eliminated from the list and therefore can't be chosen again.

I used that technique when I coached Softball. Once I selected Megan to play Center Field, she was no longer available in the Drop Down, so I couldn't accidentally use her at 2nd base also. Not only did it prevent accidental re-use, it also "cleaned up" the list so that it was easier to see who I had left. Deleting the choice from the cell puts the name back in the drop down. That's useful for those times when you realize that you have placed all of your pitchers in the outfield. ;-)

If that sounds useful, I can provide the instructions, although they are available on the web via a search.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question