View Single Post
 
Old 02-23-2022, 06:30 AM
ArviLaanemets ArviLaanemets is offline Windows 8 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