[Excel] Automatisch sorteren en wijzigen na lookup

[Dr. Strangelove]

Ik ben voor ons office management een excel bestandje met adresgegevens wat toegankelijker aan het maken; het is hele doodgewone data, maar door de vele informatie die er over een persoon wordt vastgelegd telt het inmiddels heel wat kolommen. Ik stelde voor in Access iets in elkaar te zetten, maar dat betekent dat er drie Access licenties moeten worden aangeschaft en dat vindt men wat zonde voor zoiets. Dus heb ik besloten het in Excel te doen.

Wat ik voor ogen heb is heel simpel: twee werkbladen, eentje met de ruwe data zoals die nu vastligt, en een presentatieblad. Hierin kan je via een pull-down menu de achternaam selecteren waarna alle overige data verschijnt d.m.v. de look-up / verticaal zoeken functie. Werkt prima, maar om de boel te stroomlijnen heb ik nog twee puntje op de i te zetten.

Ten eerst moet de data ook makkelijk aan te passen zijn. Ik zou een go-to functie naast de look-up cellen kunnen zetten waardoor er naar de te wijzigen cel in het andere werkblad gesprongen wordt, maar is het ook mogelijk om zonder het werkblad te verlaten de data te wijzigen?

Ten tweede is een voorwaarde voor look-up dat de datatabel alfabetisch is gesorteerd; ik kan natuurlijk een knop toevoegen genaamd 'Sorteer' of 'Update' met een macro eraan, maar is het ook mogelijk om de tabel te sorteren op het moment dat ik het Presentatie selectieblad open? Dan hoeft er nl. geen verdere handeling aan te pas te komen. Wat ik dus zoek een mogelijkheid om een macro te starten bij het selecteren van een werkblad.

[KingRichard]

De syntax voor VLOOKUP is:

code:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Als je bij range_lookup TRUE invult, zoekt VLOOKUP alleen naar exacte matches en hoeft de data niet in alfabetische volgorde te staan.

Celverwijzingen zijn éénrichtingsverkeer, dus het wijzigen van waardes in een andere sheet kan niet met standaard Excel-functionaliteit. Je zou hiervoor in ieder geval VBA moeten gebruiken; de manier waarop hangt een beetje af van je eigen voorkeuren, denk ik. Ik zou denk ik een knop erbij zetten die een InputBox laat zien waar je de nieuwe waarde kunt invullen.
Je zou misschien een dynamische hyperlink kunnen maken die zichzelf aanpast aan de hand van dezelfde waarde die VLOOKUP gebruikt. Kijk hiervoor naar MATCH. Met een beetje mazzel kan dit zelfs zonder VBA.

[Dr. Strangelove]

Dank je.

Met VLOOKUP duikt nu een ander klein probleempje op; de waardes komen goed omhoog, maar omdat ik niet sorteer zijn de waardes in het pulldown menu ook niet gesorteerd. Dat is natuurlijk niet handig. Ik zal toch automatisch moeten sorteren.

Je verhaal over celverwijzingen stel ik op prijs maar mijn totale gebrek aan programmeerervaring met VBA (of enige andere taal) en beperkte ervaring met Excel zorgen ervoor dat ik er weinig mee kan helaas...

[ Voor 5% gewijzigd door Dr. Strangelove op 06-04-2006 14:43 ]

[Verwijderd]

Dr. Strangelove schreef op donderdag 06 april 2006 @ 10:57:Ik stelde voor in Access iets in elkaar te zetten, maar dat betekent dat er drie Access licenties moeten worden aangeschaft en dat vindt men wat zonde voor zoiets.

Voor de Access runtime heb je geen licentie nodig.

[KingRichard]

Goeie tip van FFrenzy. Mocht je toch voor de VBA-oplossing willen gaan, dan kan ik je wel op weg helpen:

1. Open je spreadsheet
2. Druk op ALT-F11
3. Zoek in de Project Explorer (CTRL-R) ThisWorkbook op.
4. Dubbelklik op ThisWorkbook.
5. Je ziet nu twee dropdown boxes. Selecteer in de linker Workbook en in de rechter Open.
6. Als het goed is zie je nu
   code:
   

   Private Sub Workbook_Open()
   
   End Sub

   Verander dit in
   code:
   

   Private Sub Workbook_Open()
   
   Sheets("NaamVanSheet").Columns("A:C").Sort Key1:=Range("A1"), order1:=xlAscending
   
   End Sub

Je lijst zal gesorteerd worden iedere keer dat het bestand geopend wordt (en macro's toegestaan worden). Ik neem aan dat je zelf de juiste waarden kunt invullen in bovenstaand voorbeeld. Er is natuurlijk nog veel meer mogelijk, maar dat mag je zelf uitzoeken.

[Dr. Strangelove]

Ik dacht dat de Runtime versie het wijzigen van db bestanden niet toestond en dat je alleen data kon bekijken. Maar als je inderdaad cellen kan wijzigen is dat een optie.

Thnx King, ben er mee bezig...

Dat werkt inderdaad, maar brengt het volgende probleem weer met zich mee...

De lookups zijn afhankelijk van een sleutelwaarde die ik d.m.v. een popup menu kies. Als ik validatie -> lijst kies ben ik beperkt tot waardes in hetzelfde werkblad, daarom gebruik ik een copie van de sleutelcellen in het andere werkblad als validatiewaardes. Wanneer ik regels toevoeg op het datawerkblad dan is er niks aan de hand, dit wordt gewoon bijgewerkt in de gecopieerde cellen. Maar als ik een regel verwijder, dan wordt dit niet geupdate en krijg ik de melding #VERW!. Waarom is dit?

[ Voor 113% gewijzigd door Dr. Strangelove op 06-04-2006 16:04 ]

[KingRichard]

Dr. Strangelove schreef op donderdag 06 april 2006 @ 15:24:
Wanneer ik regels toevoeg op het datawerkblad dan is er niks aan de hand, dit wordt gewoon bijgewerkt in de gecopieerde cellen.

Weet je dat wel zeker? Ik denk dat er na het invoegen één regel meer op je datawerkblad staat dan bij de gekopiëerde cellen. De door jou ingevoegde regel zal overgeslagen worden in de verwijzingen. Dit is consequent met het verschijnen van een #VERW! als je een regel verwijdert.

[Sherlock]

Is het niet mogelijk om één werkblad te houden en daar een form aan te koppelen? Kun je de data meteen bewerken en alles is in één oogopslag te zien.

[Dr. Strangelove]

KingRichard schreef op vrijdag 07 april 2006 @ 09:38:
[...]
Weet je dat wel zeker? Ik denk dat er na het invoegen één regel meer op je datawerkblad staat dan bij de gekopiëerde cellen. De door jou ingevoegde regel zal overgeslagen worden in de verwijzingen. Dit is consequent met het verschijnen van een #VERW! als je een regel verwijdert.

Ik heb een aantal legel regels meegenomen in de gecopieerde cellen; ik heb b.v. 15 regels met contactpersonen, maar ook de 15 regels daaronder meegecopieerd. Die verschijnen dus ook onderaan in de pop-up box. Het is een work around, maar het werkt. Totdat je regels gaat verwijderen...

Belangrijker is trouwens dat ik erin slaag om regels te wijzigen. Ik heb de volgende VBA regeltjes gevonden maar deze zijn voor een specifieke cel, en ik moet juist hebben dat er een soort Vlookup ingebakken zit zodat het scriptje niet een specifieke cel pakt maar de corresponderende regel.

Sub auto_open()
x = InputBox("which scheme?", "deposit calc", vbOKCancel)
Sheets("Sheet1").Range("A1") = x
End Sub

Is het niet mogelijk om één werkblad te houden en daar een form aan te koppelen? Kun je de data meteen bewerken en alles is in één oogopslag te zien.

Geen idee, nooit met forms gewerkt in Excel. Ik zal eens kijken.

[ Voor 12% gewijzigd door Dr. Strangelove op 07-04-2006 12:15 ]

[KingRichard]

Dr. Strangelove schreef op vrijdag 07 april 2006 @ 12:14:
[...] Ik heb een aantal legel regels meegenomen in de gecopieerde cellen; ik heb b.v. 15 regels met contactpersonen, maar ook de 15 regels daaronder meegecopieerd. Die verschijnen dus ook onderaan in de pop-up box. Het is een work around, maar het werkt. Totdat je regels gaat verwijderen...

Ik geloof dat we een beetje langs elkaar heen aan het praten zijn. Als je een paar keer op Blad 1 regels toevoegt en verwijdert en ondertussen goed de verwijzingen op Blad 2 in de gaten houdt, zul je begrijpen wat ik bedoel.
Een echt goede, structurele oplossing is hiervoor niet met standaard Excel-functionaliteit. Vandaar dat een database de beste oplossing zou zijn. Second best is de oplossing van Sherlock. Als VBA nieuw voor je is, is het behoorlijk wat gepiel, maar het is zeker te doen. Bovendien is de online help van VBA behoorlijk goed. En je hebt GoT voor als je er niet uitkomt, natuurlijk.

[Dr. Strangelove]

Het gaat prima met toevoegen, zolang ik natuurlijk de range van de VLOOKUP functie niet verlaat. Het probleem ontstaat met deleten van regels.

Het is enigszins frustrerend allemaal want wat ik wil is zo simpel in Access... ik heb wat gekeken naar forms maar i.t.t. wat je zegt vind ik de online hulp in Excel waardeloos. Er wordt wel uitgelegd wat een form is maar niet hoe ik het vervolgens moet implementeren.

Nog een klein puntje: als VLOOKUP een leeg veld ophaalt zet hij een 0 neer; hoe kan ik ervoor zorgen dat er iets van <niet ingevuld> of desnoods helemaal niets in dat veld komt?

[ Voor 18% gewijzigd door Dr. Strangelove op 07-04-2006 13:34 ]

[KingRichard]

Dr. Strangelove schreef op vrijdag 07 april 2006 @ 13:32:
[...] ik heb wat gekeken naar forms maar i.t.t. wat je zegt vind ik de online hulp in Excel waardeloos. Er wordt wel uitgelegd wat een form is maar niet hoe ik het vervolgens moet implementeren.

Om eerlijk te zijn: ik ben aardiger voor je dan normaal vanwege je respectabele postcount. Anders had ik gezegd: "Je moet wel een béétje je best doen, Dr. Strangelove!" Maar goed, hoe denk jij dat een form geïmplementeerd zou moeten worden?
Een duwtje in de richting:[list=1]• Ga naar de VBA-editor.• Maak een formuliertje aan.• Knal er een een ComboBox in.• Zet in de Code van de Form:

Visual Basic:

Private Sub UserForm_Initialize()

Dim rCell As Range
Dim shDataSheet As Worksheet

Set shDataSheet = ThisWorkbook.Sheets("Sheet1")

For Each rCell In shDataSheet.Range("A:A").SpecialCells(xlCellTypeConstants).Cells
    Me.ComboBox1.AddItem rCell.Value
Next rCell

End Sub

Nu heb je een ComboBoxje met alle gegevens uit de eerste kolom van Sheet 1. Voeg in de form wat TextBoxjes enzo toe en je bent al op de helft.

Nog een klein puntje: als VLOOKUP een leeg veld ophaalt zet hij een 0 neer; hoe kan ik ervoor zorgen dat er iets van <niet ingevuld> of desnoods helemaal niets in dat veld komt?

code:

=IF(VLOOKUP(D1,A:B,2,FALSE)="","Niet ingevuld",VLOOKUP(D1,A:B,2,FALSE))

[ Voor 14% gewijzigd door KingRichard op 10-04-2006 12:46 . Reden: een paar loze Dims verwijderd ]