Excel: Waarde mag in werkmap eenmalig voorkomen

[JdM]

Mijn vraag
Ik wil graag in een excel werkmap ervoor zorgen dat een bepaalde waarde (in mijn geval een factuurnummer) slechts eenmalig mag voorkomen en dat er een melding verschijnt wanneer de code al is gebruikt.
Nog mooier zou het zijn als ik begin met invoeren van het factuurnummer, dat er een lijst verschijnt met het eerstvolgende beschikbare nummer, maar ik heb geen idee of dat kan.
Initieel is het idee om ervoor te zorgen dat ik in de werkmap niet de fout kan maken om twee keer hetzelfde factuurnummer te gebruiken.

Het is een relatief grote werkmap, waarop verschillende plekken factuurnummers kunnen worden ingevoerd. Het is daarom geen optie om hiervoor losse cellen aan te gaan duiden, omdat dat teveel werk is en omdat de werkmap nogal eens verandert, waardoor er steeds cellen bij komen die dezelfde controle moeten hebben. Ik wil daarom de hele werkmap onder controle houden, indien mogelijk.

Relevante software en hardware die ik gebruik
Office Pro-plus 2016

Wat ik al gevonden of geprobeerd heb
Via deze site kom ik op het maken van een formule in de volgende vorm =Aantal.Als($B$3:$B$11;B3)=1 en met behulp van de uitleg aldaar komt dit redelijk in de buurt. Ik wil alleen het cellenbereik voor de volledige werkmap laten gelden. Is er een methode om in deze formule de hele werkmap aan te duiden, of is er een andere methode die ik nog niet gevonden heb?

[F_J_K]

Je gebruikt nu letterlijk het bereik van het voorbeeld. Pas aan naar jouw situatie?

Algemene tip: nooit letterlijk kopiëren.

[Belindo]

Je zult ergens alle factuurnummers moeten gaan bijhouden. Dan kun je inderdaad met een simpele Countif kijken of het factuurnummer wat je wilt gaan gebruiken al in de lijst met gebruikte nummers voorkomt. In jouw geval wil je overal kijken of het nummer ergens staat. Dit heeft twee nadelen:
1: je gaat een enorm bereik aan cellen doorzoeken met een formule, dit zal voor erg veel latency zorgen
2: wellicht gaan er sheets toegevoegd worden, dan moet je elke keer je formule updaten om ook in de nieuwe sheet(s) te zoeken.

De makkelijkste oplossing is één tabel met daarin de gebruikte nummers. Dan kun je óf daarin zoeken of het nummer al gebruikt is, maar nog makkelijker; je nieuwe nummer baseren op het hoogste nummer in de lijst plus één.

Minder makkelijk zou zijn om een klein Visual Basic scriptje te schrijven die in de gehele file zoekt op dat nummer. Dan zou je na je factuurnummer te typen op een knop kunnen drukken om de hele file te doorzoeken. Of je laat het script automatisch draaien nadat je een factuurnummer hebt ingevuld.

[JdM]

F_J_K schreef op donderdag 8 april 2021 @ 11:55:
[...]Pas aan naar jouw situatie?

Algemene tip: nooit letterlijk kopiëren.

Dat is dus de vraag, hoe doe ik dat op een dusdanige wijze dat de hele werkmap wordt gecontroleerd en niet allen B3totB11 zoals in het voorbeeld

Wat bedoel je met je algemene tip?

[JdM]

Belindo schreef op donderdag 8 april 2021 @ 11:58:
Je zult ergens alle factuurnummers moeten gaan bijhouden. Dan kun je inderdaad met een simpele Countif kijken of het factuurnummer wat je wilt gaan gebruiken al in de lijst met gebruikte nummers voorkomt. In jouw geval wil je overal kijken of het nummer ergens staat. Dit heeft twee nadelen:
1: je gaat een enorm bereik aan cellen doorzoeken met een formule, dit zal voor erg veel latency zorgen

Ik kan het bereik wel enigszins beperken van Rij A-Z en kolom 1-500, daarin zitten de te controleren waardes. Latency is niet heel erg, het gaat hier niet om snelheid.

2: wellicht gaan er sheets toegevoegd worden, dan moet je elke keer je formule updaten om ook in de nieuwe sheet(s) te zoeken.

De makkelijkste oplossing is één tabel met daarin de gebruikte nummers. Dan kun je óf daarin zoeken of het nummer al gebruikt is, maar nog makkelijker; je nieuwe nummer baseren op het hoogste nummer in de lijst plus één.

Een pulldown lijst is niet handig, dan moet ik nog steeds op de plekken waar factuurnummers ingevoerd worden, pulldown menus maken.

Minder makkelijk zou zijn om een klein Visual Basic scriptje te schrijven die in de gehele file zoekt op dat nummer. Dan zou je na je factuurnummer te typen op een knop kunnen drukken om de hele file te doorzoeken. Of je laat het script automatisch draaien nadat je een factuurnummer hebt ingevuld.

2 digitale linkerhanden, scripts zijn aan mij niet besteed.

[g0tanks]

Zonder één centrale plek waar alle factuurnummers staan wordt het erg lastig zonder VBA.

JdM schreef op donderdag 8 april 2021 @ 12:08:
[...]

Ik kan het bereik wel enigszins beperken van Rij A-Z en kolom 1-500, daarin zitten de te controleren waardes. Latency is niet heel erg, het gaat hier niet om snelheid.

Je hebt hier rijen en kolommen omgedraaid, maar zeg je nou dat in één werkblad de factuurnummers ook over allerlei kolommen zijn verspreid? Ik zou verwachten dat in een nette tabel er één kolom is voor factuurnummer.

[GRDavies75]

Belindo schreef op donderdag 8 april 2021 @ 11:58:
[...] je nieuwe nummer baseren op het hoogste nummer in de lijst plus één.

Dit is ook het eerste waar ik aan dacht

@JdM Ik heb even nog snel gezocht, maar kon zelf niet zo snel iets vinden waarmee je het hele bestand kan referencen. Wat wel kan, maar dan ben je nog steeds gelimiteerd aan 1 worksheet, je kan namelijk of hele kolommen of hele rijen verwijzen dmv A:A (Gehele kolom A) of 13:13 (gehele 13e rij), maar ook A:YY (Dus van kolom A t/m kolom YY) eventueel met de naam van het werkblad, dus Werkblad1!A:YY moet je denk ik heel ver mee komen. Of een stuk dichterbij je gewenst gedrag komen.