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

Watch it in a short video

A drop-down list that depends on another drop-down list 3:08

Picture an order form where you choose a category first and then only the varieties in that category: choose Tulips and you only see Strong Gold and Purple Prince, 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 use the florist Bloemisterij Het Zonnetje. On a worksheet called Lists, the categories are 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. So for "Dried bouquet" you use the name Dried_bouquet, with an underscore. You 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, under Source type =Categories and click OK.
  3. Select the cells for the variety, C2:C50, and open Data Validation again.
  4. Choose List and under Source type: =INDIRECT(SUBSTITUTE(B2," ","_"))
  5. Click OK.

Tip: choose a category in B2 first, for example Tulips, and only then create the second drop-down list. If B2 is still empty, Euro-Office tells you when you click OK that the list source must be a delimited list or a reference to a single row or column. If you get that message, close it, choose a category in B2 and create the drop-down list in C2:C50 again.

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 of 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 in column D use =INDIRECT(SUBSTITUTE(C2," ","_")) as the source.

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 category text and the name don't match. Watch out for spaces, hyphens and a name that starts with a number; that isn't allowed in a name.
  • An old choice stays put: 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 under 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…