Skip to content

Cookie settings

Choose which kinds of cookies you allow. You can change your choice at any time via Cookie settings at the bottom of every page. Privacy statement

Necessary

Always on

Needed to log in, keep your cart and remember your choices. These are always on.

Which cookies?
  • weboke_sessie, weboke_klant, weboke_remember: your session and staying logged in
  • weboke_basket: your cart
  • wkBtwIncl: showing prices with or without VAT
  • wok_cookies: your cookie choice, 12 months

Measures which ad leads to a visit or order, so we do not pay for ads that do nothing.

Which cookies?
  • Google Ads via Google Tag Manager (_gcl_au, _gcl_aw), up to 90 days
  • OpenAI Ads, ads in ChatGPT
  • wok_aff: which partner referred you to us (affiliate programme), 60 days

4.6

Country and language

Each country has its own site, with prices and VAT for that country.

View

A drop-down list that depends on another drop-down list

Picture an order form where you first choose a category and then only see the varieties in that category: choose Tulips and you see only Strong Gold and Purple Prince, and no roses. This is called a dependent drop-down list. A regular drop-down list is covered in Data validation and drop-down lists; here you build two that work together. It uses named ranges and the INDIRECT function.

As an example, we'll use Bloemisterij Het Zonnetje. A worksheet called Lists has the categories as headings, with the varieties below them:

ABCD
1RosesTulipsSeasonalDried bouquet
2AvalancheStrong GoldPeonyLavender
3Red NaomiPurple PrinceRanunculusStrawflower
4Pink FloydSunflower

Step 1: give each list of varieties a name

  1. Select the varieties under Roses, without the heading: A2:A4.
  2. On the Formula tab, click the arrow next to Named ranges and choose Define name.
  3. Under Name, type exactly the text of the heading: Roses. Leave Scope set to Workbook and click OK.
  4. Do the same for Tulips (B2:B3) and Seasonal (C2:C4).
The New name window
Name, Scope (Workbook) and the Data range of the selected cells

A name can't contain a space. For "Dried bouquet", you therefore use the name Dried_bouquet, with an underscore. You'll deal with that in step 3.

Step 2: give the headings a name too

Select A1:D1 and, in the same way, give it the name Categories. Then check everything using the arrow next to Named ranges and Name manager.

Name manager
One name per list of varieties, and Categories for the headings

Step 3: the two drop-down lists

  1. Go to the order sheet and select the cells for the category, for example B2:B50.
  2. On the Data tab, click Data Validation. Under Allow, choose List, type =Categories under Source and click OK.
  3. Select the cells for the variety, C2:C50, and open Data Validation again.
  4. Choose List and type under Source: =INDIRECT(SUBSTITUTE(B2," ","_"))
  5. Click OK.
Data Validation with INDIRECT
Under Source, INDIRECT refers to the name that matches the chosen category

How does it work? If B2 contains Tulips, INDIRECT turns that text into a reference to the name Tulips, and the drop-down list shows B2:B3 from the Lists worksheet. SUBSTITUTE first replaces a space with an underscore, so "Dried bouquet" finds the name Dried_bouquet. Use B2 without dollar signs: that way each row looks at its own category.

The dependent drop-down list in action
B2 contains Tulips, so the list in C2 shows only the two tulips

A third level

Want people to choose a colour after the variety as well? Create a list of colours for each variety, give it the name of the variety (for example Red_Naomi), and use =INDIRECT(SUBSTITUTE(C2," ","_")) as the source in column D.

Pitfalls

  • The heading appears in the list: you included the heading in the named range. Adjust the range in Name manager.
  • The list stays empty: the text of the category and the name don't match. Watch out for spaces, hyphens and names that start with a number; that isn't allowed in a name.
  • An old choice remains: if you later choose a different category in B2, the variety in C2 stays, even if it no longer fits. Clear C2 or choose again.
  • A new variety isn't included: if you type a fourth rose below Pink Floyd, it falls outside the name Roses (A2:A4). Adjust the range in Name manager.

One moment…

Set up your domains

For each domain, arrange the nameservers, the link to your hosting and who becomes the owner.

Loading your domains…