Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #4  
Old 02-23-2022, 06:30 AM
ArviLaanemets ArviLaanemets is offline multiple validation on a column to avoid duplicate entries Windows 8 multiple validation on a column to avoid duplicate entries Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

Your unique numeric entries are entered into column A of sheet Working. And probably you have a header (a string) for this column too.
On some hidden sheet (e.g. Hidden), into some cell (e.g. A1) enter formula, which calculates next unique value, like
Code:
=MAX(Working!$A:$A) +1
Create a Name (e.g. nNextID), which refers to cell with formula
Code:
nNextID = Hidden!$A$1
Activate any cell (e.g. A2) on sheet Working, and create a Name (e.g. nCurrID), like
Code:
nCurrID = Working!$A2
I.e. the Name returns the value of column A of active row in sheet Working. Such Names are referred to as Dynamic Names.
Create a Name (e.g lIDList), like
Code:
lIDList = IF(nCurrID = "", nNextID, nCurrID)
For column A of sheet Working (for Datarange of this column), create a Data Validation List based on lIDList. Like
=lIDList

Now, whenever you activate this Data Validation List on sheet working, when the cell has any ID value entered previously, this value is only option, and when the cell is empty, the next ID value is the option.

You can also add another IF() level for lIDList, which checks is the active cell in column A, and when not, returns some error message instead ID value.
Reply With Quote
 

Tags
data validation, duplicate entries



Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple validation on a column to avoid duplicate entries How to avoid duplicate table headers while mail merge records? everwisher Mail Merge 2 01-04-2019 04:50 AM
Can Excel find Duplicate entries when only part of the cell's data is a duplicate of another cell? jsisley Excel 1 07-21-2017 09:20 AM
Duplicate entries yourforester Outlook 1 11-15-2014 10:44 PM
Calendar - Duplicate Entries castow Outlook 0 02-05-2014 02:53 PM
How Can I Flag Duplicate Date Entries? tatihulot Excel 5 11-16-2012 12:19 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft