Validatie werkt niet in Excel 365

[formatfrits]

Beste Excel-experts,

Als ik een validatiecel aanmaak met lijst en de bron van vanuit een lijst met een gedefinieerde naam, met de volgende formule :

=OFFSET(naam;MATCH(LEFT(b2;LEN(b2));LEFT(namen;LEN(b2));0);0;SUMPRODUCT(--(LEFT(namen;LEN(b2))=b2));1)

Dan werkt dit. Als ik daarna deze sheet opsla, afsluit en weer open, dan werkt het niet meer. Wie kan mij helpen om dit werkend te houden?

Groeten,
Frits

 

[AlexCEL]

Plaats eens een voorbeeldbestand.

 

[formatfrits]

Dit is een simpel voorbeeldje

 

[AlexCEL]

Wat moet de validatie doen? Alles met beginletter J filteren?

Welke Excelversie gebruik je?

 

[formatfrits]

Hoi Alex,

De versie is Excel 365, en ja op de eerste of meerdre letters filteren, zodat de validatielijst korter wordt. Originele lijst beslaat meer dan 350 namen.
Validatiecel staat op sheet 1, werkt totdat ik het opsla en weer open.

Groeten,
Frits

 

[HSV]

Die bug zit er al in sinds Excel 2007 en dacht dat het er inmiddels wel uit zou zijn.

Oplossing:

=OFFSET(Naam;MATCH(LEFT(Sheet1!B2;LEN(Sheet1!B2));LEFT(Namen;LEN(Sheet1!B2));0);0;SUMPRODUCT(--(LEFT(Namen;LEN(Sheet1!B2))=Sheet1!B2)[COLOR=#ff0000]+0*TODAY()[/COLOR]);1)

 

[formatfrits]

Goedenavond Harry,

Bedankt voor deze oplossing, deze formule in de gegevensvalidatie gezet, helaas blijft hetzelfde probleem bestaan.
Het werkt, maar na opslaan en weer openen werkt de validatie helaas niet meer.

Groeten,
Frits

 

[HSV]

Werkt hier prima in Excel 365 na opslaan en heropenen.

1. Enig verschil misschien; ik heb het een naam gegeven en in de formulebalk bij de validatie de naam geplaatst.

2. Indien er al een naam staat in B2 dan deleten en een nieuwe letter typen.

 

[formatfrits]

Goedenavond Harry,

Ik heb hier ondertussen het een en ander uitgeprobeerd. Hier thuis werk ik met Excel 2016, helaas geen excel 365 bij de hand, het probleem blijft zich ook hier voordoen.
Overigens had ik deze formule al eens eerder gebruikt, mooie sheet gemaakt, tot mijn tevredenheid werkend. Geïmplementeerd in de sheets op het werk, helaas konden zij door deze bug niets meer doen met deze sheet. De oude formule dus weer teruggezet, helaas zie je dan de hele lijst.
Is er misschien een andere formule, waarmee je de validatielijst inkort door de eerste letter(s) in te tikken? En dan liefst 1 die nog werkt na opslaan en weer openen.

Groeten,
Frits

 

[HSV]

Goedenavond Frits,

In Excel 2007 werkte ik hier ook mee en dat werkte uitstekend.
Misschien dat dit nog helpt, want zo had ik het staan in Excel 2007.

=OFFSET(Naam;MATCH(LEFT(Sheet1!B2;LEN(Sheet1!B2));LEFT(Namen;LEN(Sheet1!B2));0);0;SUMPRODUCT(--(LEFT(Namen;LEN(Sheet1!B2))=Sheet1!B2)[COLOR=#ff0000]*1+0*TODAY()[/COLOR]);1)

 

[formatfrits]

Hallo Harry,

Helaas, wat er ook aan de formule aangepast wordt, het werkt totdat het opgeslagen wordt en weer geopend.
Hierna werkt het dus niet meer. Enige wat ik hier op kan verzinnen is in workbook-open een macro die op O.K. klikt bij de datavalidatie.

Groeten,
Frits

 

[sylvester-ponte]

Frits, als je het filter verderop zet (zie gele cellen) en de validatie naar het gele filter laat verwijzen (alleen bovenste cel met # is nodig) werkt het in 365.

 

[formatfrits]

Hoi Sylvester,

Ik hoop dat ik morgen wat tijd kan vinden om dit te testen in Excel 365, ik heb nu helaas alleen 2016 bij de hand.
ik heb overigens de vraag gesteld met een simpel voorbeeldbestandje, er zijn echter sowieso 3 validatielijsten in het originele bestand.

Groeten,
Frits

 

[HSV]

Heb je de cellen wel eerst leeggemaakt zoals ik voorstelde?
Daarna opslaan en opnieuw openen.
De +0*vandaag() berekend namelijk bij het openen van het bestand en werkt hier al een x aantal jaren.
+0*nu() zal het ook wel moeten doen.

Sinds ik insider ben van Excel 365 heb je helemaal geen formule meer nodig; zie:
https://www.helpmij.nl/forum/showthread.php/963712-validatie-met-zoekfunctie-OPLOSSING!

 

[formatfrits]

Hoi Harry,

Ik heb de validatiecel inderdaad leeggemaakt en opgeslagen, helaas werkt dat niet. Als dit zou werken, zou dat dan iedere keer gedaan moeten worden? Er zouden namelijk een enorm aantal mensen met bestanden gaan werken, waarin deze manier van valideren gebruikt zou moeten worden.
Het artikel over deze functie in een nieuwe versie van Excel heb ik ook gelezen, ik hoop er snel gebruik van te kunnen gaan maken.
Morgen of anders ergens verderop in de week nog eens in de Excel 365-versie, welke wij op het werk gebruiken, testen, misschien dat er dan ergens een bitje de goede kant op valt.

Groeten,
Frits

 

[HSV]

Nee, je hoeft het niet steeds opnieuw in te stellen.

Voordat ik nog geen gebruik maakte van die +0*vandaag() moest ik het met F2 en Enter ook steeds opnieuw instellen.
Vreemd dat het in Excel 2016 niet werkt, helaas dus.

 

[snb]

Wat gebeurt er met deze validatieregel ?

Kijkt naar de eerste match en de volgende 5 items in een gesorteerde lijst.

=OFFSET(Namen;MATCH(B2&"*";Namen;0)-1;;5)