[Excel] Hoe maak ik een dropdown list vanuit een ander blad?

[Anoniem: 296662]

In een werkboek heb ik het volgende excel blad (naam: voedingswaarden):

code:

            A                 B       C       D
  +-----------------------+-------+--------+-----+
1 | product (100 gram)    | kcal  | koolh. | vet |
  +-----------------------+-------+--------+-----+
2 | aardappel             | 122,0 | 16,0   | 5,5 |
  +-----------------------+-------+--------+-----+
3 | biefstuk              | 127,0 | 23,8   | 0,7 |
  +-----------------------+-------+--------+-----+
4 | tonijn (blik)         | 190,0 |  0,0   | 9,6 |
  +-----------------------+-------+--------+-----+
5 | etc...                |       |        |     |
  +-----------------------+-------+--------+-----+

Op een tweede blad (naam: dieet) wil ik graag per dag een dieet samenstellen. Ik zou dit blad graag koppelen aan het blad 'voedingswaarden', als volgt:

code:

       A              B             C         D       E       F
  +----------+------------------+---------+-------+--------+-----+
1 | maaltijd | ingredient       | gewicht | kcal  | koolh. | vet |
  +----------+--+---------------+---------+-------+--------+-----+
2 | ontbijt  |\/| tonijn (blik) | 100     | 190,0 |    0,0 | 9,6 |
  +----------+--+---------------+---------+-------+--------+-----+
3 |          |\/| aardappel     |  50     |  61,0 |    8,0 | 2,8 |
  +----------+--+---------------+---------+-------+--------+-----+

Wat je ziet is:

• elke regel in kolom 'ingredient' heeft een dropdown menuutje waar alle producten uit het voedingswaarden-blad in staan;
• in de kolom ernaast kan ik het gewicht invullen. Met behulp van de waarden uit het voedingswaarden-blad, worden de cellen kcal, koolh. en vet ingevuld;
• zie de 2e regel (aardappel). Ik heb 50 gram ingevuld. Excel rekent dan uit: 122 / 100 * 50 = 61 kcal, etc.

Ik hoop dat iemand mij op weg kan helpen met het volgende:

• hoe kan ik het dropdown-menuutje creeren?
• hoe kan ik de cellen in blad 'dieet' automatisch laten uitrekenen mbv de waarden uit blad 'voedingswaarden'?

Ik dacht dit eerst maar webbased te schrijven (op mijn thuisservertje), want dan weet ik wel hoe het moet (met een beetje php / sql). Maar dat leek me wat overdreven, omdat dit volgens mij precies is waarvoor Excel gemaakt is. Ik heb alleen geen ervaring met koppelingen tussen verschillende bladen. Ik hoop dat iemand mij op weg kan helpen. Ik hoop dat het duidelijk is dat ik geen kant-en-klare oplossing vraag, maar eerder tips / links, etc

[Nitai]

Je moet je een beetje gaan inlezen in de functies VLOOKUP, HLOOKUP en LOOKUP... oftewel verticaal zoeken, horizontaal zoeken en zoeken. De Help van Excel is uitstekend met ontzettend veel voorbeelden waar je zulke basis dingen binnen een paar minuten voor elkaar hebt.

[--Niels--]

Voor het ophalen van de kcal, koolh. en vet kun je zoals Nitail idd zoekfuncties gebruiken. In de Nederlandse versie heet de functie trouwens vert.zoeken() voor vertikaal zoeken (met deze moet het lukken).

Voor je dropdown menuutje, kun je gegevensvalidatie gebruiken. Waar je dat kunt vinden hangt van je versie van Excel af, in 2007 zit het onder het tabje 'Gegevens'. Als daar onder instellingen bij 'toestaan', 'lijst' selecteert, kun je een dropdown menuutje maken.

[Anoniem: 9449]

Kijk, dat is nu eens een duidelijke TS!

Dropdown menu: validatie (list), werkt perfect.

Uitrekenen (voor cel D2): {=sum(if(B2=blad1!A:A,blad1!B:B))*C2/100}

[Anoniem: 296662]

--Niels-- schreef op donderdag 01 oktober 2009 @ 13:02:
[...]
Voor je dropdown menuutje, kun je gegevensvalidatie gebruiken. Waar je dat kunt vinden hangt van je versie van Excel af, in 2007 zit het onder het tabje 'Gegevens'. Als daar onder instellingen bij 'toestaan', 'lijst' selecteert, kun je een dropdown menuutje maken.

Klopt, en ik heb daar verschillende tutorials over gevonden (o.a. http://excelonline.wordpr...n-menu-in-een-excel-cell/ en http://office.tizag.com/excelTutorial/exceldropdownlist.php) maar deze laten slechts zien hoe je een dropdown menuutje maakt als de source-cellen en de cellen waar je een dropdown menu wilt, in hetzelfde blad staan. Ik kan niet vinden hoe ik een dropdown menu op blad 'dieet' laat verschijnen, met als source cellen, cellen van blad 'voedingswaarden'. Het is niet mogelijk om, als het validate dialoogscherm in beeld is, een ander blad te selecteren. Ik hoop dat iemand hier meer over weet, want alle gegooglede resultaten leveren slechts eenzelfde soort tutorials op als de twee die ik noemde...

[ Voor 4% gewijzigd door Anoniem: 296662 op 01-10-2009 13:10 ]

[Anoniem: 9449]

VLOOKUP werkt in dit geval ook prima trouwens omdat de data goed gerangschikt is (van links naar rechts).

[Anoniem: 226204]

Hierover is heel veel te vinden op google en excel help.
je moet ze inderdaad eerst opzoeken met h zoekn en v zoeken.
Anders moet je op google in het engels zoeken, krijg je meer results.

Maar dit is niet zo heel moeilijk in elkaar te zetten, heb ik namelijk ook nog gedaan toen ik nog op school zat 4 jaar geleden zoiets dergelijks als wat jij nu wil

[Anoniem: 113297]

als je een naam (invoegen>naam>definiëren...) maakt die verwijst naar de ingrediëntenlijstkolom op blad 'voedingswaarden', dan kan deze naam vervolgens gebruikt worden voor de validatielijst op blad "dieet'.

[Anoniem: 296662]

Anoniem: 113297 schreef op donderdag 01 oktober 2009 @ 13:21:
als je een naam (invoegen>naam>definiëren...) maakt die verwijst naar de ingrediëntenlijstkolom op blad 'voedingswaarden', dan kan deze naam vervolgens gebruikt worden voor de validatielijst op blad "dieet'.

Thanks, heb nog even verder gezocht en kwam met deze info op http://www.msoffice-tutor...excel-drop-down-list.html Gelukt

Nu nog met vlookup / hlookup de de kcal, etc. uitrekenen.

BTW: begrijp ik nu goed, dat als ik de voedingswaarden-lijst langer maak, ik telkens opniew de naam (ik noemde het voedingswaarden_producten) moet definieren? Het is dus niet mogelijk om te zeggen "neem kolom B vanaf cel B2 tot onderaan en dat is de source voor de dropdown? Dus, dat je niet expliciet een laatste cel definieert, maar dat Excel zelf doorgaat tot aan de laatste rij...?

[Anoniem: 113297]

toch wel, door middel van een dynamische naam (dynamic range name) die zich automatisch aanpast.

[pedorus]

Als in (bij alleen tekst):

code:

=voedingswaarden!$A$2:
  INDEX(voedingswaarden!$A:$A,MATCH(REPT("z",255),voedingswaarden!$A:$A))

Dollartekens zijn zeer belangrijk.

[Anoniem: 296662]

pedorus schreef op donderdag 01 oktober 2009 @ 13:39:
Als in (bij alleen tekst):

code:

=voedingswaarden!$A$2:
  INDEX(voedingswaarden!$A:$A,MATCH(REPT("z",255),voedingswaarden!$A:$A))

Dollartekens zijn zeer belangrijk.

Wow, dat vind ik weer even ingewikkeld. Ik heb net zelf het volgende gefabriceerd:

code:

=VLOOKUP(dieet!B5;voedingswaarden!$A$2:$K$25;2;FALSE)

Dat werkt perfect Hoe moet ik jouw oplossing lezen? Zitten daar stukjes in om errors tegen te gaan? (max. 255 tekens bv.)?

[pedorus]

Een goede methode voor een dynamic range op tekst is:

code:

=StartCel:INDEX(LaatsteKolom,MATCH(REPT("z",255),ZoekenInKolom))

Waarbij MATCH(REPT("z",255),ZoekenInKolom) het laatste rijnummer teruggeeft (zzzz... komt als laatste in het alfabet), Index dat omzet in de juiste eindcel, en startcel het begin van de range aangeeft. Na het evalueren van die 3 formules staat er dus gewoon het standaard startcel:eindcel. Dit werkt dus ook voor ranges van meer dan 1 kolom.

[onkl]

Zolang je geen lege cellen in je ingredientenlijst hebt, kan je ook gewoon een aantalarg /CountA gebruiken voor je dynamische naam. In je voorbeeld (negeer cel B1:
=OFFSET(B2,0,0,COUNTA(B:B),1)

[pedorus]

Dat kan inderdaad ook, en is bij combinaties van tekst, nummer, enz. ook soms handig. Het nadeel is enkel dat daadwerkelijk alle cellen afgegaan worden (trager), en dat het dus misgaat als er ook maar 1 lege cel tussen staat..

[onkl]

pedorus schreef op donderdag 01 oktober 2009 @ 15:21:
Dat kan inderdaad ook, en is bij combinaties van tekst, nummer, enz. ook soms handig. Het nadeel is enkel dat daadwerkelijk alle cellen afgegaan worden (trager), en dat het dus misgaat als er ook maar 1 lege cel tussen staat..

"COUNTA" is iha een duidelijk snellere functie dan iets als bijvoorbeeld "MATCH", waarbij bovendien het "REPT zzz" construct zeer foutgevoelig (begin bijvoorbeeld eens een cel met een leesteken met een hogere asciiwaarde dan z) is én ervanuit gaat dat de bronlijst alfabetisch gesorteerd is, wat in de TS duidelijk niet het geval is. Maar, inderdaad, je moet erg goed oppassen voor lege cellen.

Wat overigens verreweg de eenvoudigste oplossing voor TS is, is heel kolom B benoemen als named range. Nadeel is dan dat de kopregel, ingrediënt, dan ook opduikt in je dropdown.

[pedorus]

onkl schreef op donderdag 01 oktober 2009 @ 17:37:
[...]

"COUNTA" is iha een duidelijk snellere functie dan iets als bijvoorbeeld "MATCH"

Onzin, het is juist andersom, vooral bij langere kolommen: COUNTA is O(n), MATCH is O(log(n)).

, waarbij bovendien het "REPT zzz" construct zeer foutgevoelig (begin bijvoorbeeld eens een cel met een leesteken met een hogere asciiwaarde dan z) is én ervanuit gaat dat de bronlijst alfabetisch gesorteerd is, wat in de TS duidelijk niet het geval is.

Probeer het eens uit, sortering is niet nodig. Welk speciaal teken had je in gedachten?
Bij alleen cijfers, een datum, true, of false op de laatste regel werkt het niet, dat klopt.

[onkl]

He. Da's leuk. Ongedocumenteerd weliswaar, maar zo is match een alleszins nuttige functie. Ben wel benieuwd naar het sorteercriterium, 't duidelijk niet de ASCIItabel oid, want accolades etc. geven geen probleem.

Ben nog niet geheel overtuigd van de performance, stringvergelijkingen lijken me intiutief meer tijd te vreten dan leeg/niet leeg checks (ergo: wat is de "O"), maar voor dagelijks gebruik heb ik deze onderschat.