Ask Your Question

[Calc] interactive drop-down? [closed]

asked 2015-04-02 15:31:37 +0100

LibreGuy gravatar image

Hi guys,

I'm not sure if what I want is possible, but maybe anyone of you can tell me if it is.

Lets say in Calc we create 2 tabs. In the second tab I would like to create "categories" with options, for example:

     A      B   
1  color   size
2  red     small
3  blue    medium
4  green   large

The first row acts as a "category", in this case "color" and "size" and the underlaying cells are the options for that specific category. Now what I would like to do, is on the first tab being able to select a category. Once I picked a category, in the next cell I would like to choose one of the options, so you get something like this:

     A           B   
2  color      blue
3  size       small

Can this be done?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-19 21:40:09.714392


May be you mean =VLOOKUP($B$2;$Sheet1.$A$2:$B$4;2;0)?

JohnSUN gravatar imageJohnSUN ( 2015-04-02 16:34:45 +0100 )edit

4 Answers

Sort by » oldest newest most voted

answered 2015-04-02 18:12:47 +0100

Regina gravatar image

You can make the formula in Validity > Cellrange independent of category and item count. To get this, you need to define names for the categories cellrange and the items cellrange and hold the categories and items values in a separate part of the sheet.

For example (see attached file)
Name myCategory for F1:G1, holding Categories "Color" and "Size" and name myItems for F2:G4 holding column "red","blue","green" in F and column "small","medium","large" in G.

The formula for validity in A2 is myCategory.
The formula for validity in B2 is INDEX(myItems;0;MATCH(A2;myCategory;0))
When you add or remove categories or items, you only need to adapt the information in the definition of the names.

edit flag offensive delete link more


This works nice. I tried the trick of defining ranges. It worked. Better yet ... I named the first row of a tab as category and all the other cells (the full sheet without the first row) as options. This seems to work correctly and I don't have to change the definitions of the names anymore. Every new category or option will be picked up automatically. Pretty cool :) Thanks all for your contributions. This will help me a lot in the feature!

LibreGuy gravatar imageLibreGuy ( 2015-04-02 21:25:45 +0100 )edit

answered 2015-04-02 16:40:11 +0100

karolus gravatar image

select Sheet2.A1:B4 and →insert→Names→create... from ..[x]top row

in Sheet1.A2 do →Data→Validity...Cellrange: $Sheet2.$A$1:$B$1 copy down in this Column

in Sheet1.B2 do →Data→Validity...Cellrange: INDIRECT(A2) copy down in this Column

edit flag offensive delete link more

answered 2015-04-02 17:00:56 +0100

LibreGuy gravatar image

Holy crap Karolus, that is very nice!!!

One last question. This is already very good, but when I add a category with options, I will have to repeat all these steps. Is there a way I can add a category and options without having to repeat these steps?

edit flag offensive delete link more


Creating the Named Range is done in 3 seconds.

the Data-Validity in Col A can be do in Advance with Cellrange: $Sheet2.$A$1:$Z$1

Data-Validity in Col B need nothing additional

karolus gravatar imagekarolus ( 2015-04-02 17:50:25 +0100 )edit

Allright, cool. Many thanks!

LibreGuy gravatar imageLibreGuy ( 2015-04-02 17:55:41 +0100 )edit

answered 2015-04-02 18:56:32 +0100

Lupp gravatar image

updated 2015-04-02 19:04:59 +0100

You get very high flexibiity defining calculated validity ranges with the help of OFFSET. Any validation depending on the content of some other cells is logically doubtable, however. A formerly valid content (selected from dropdown e.g.) will persist a change of a cell the validation should depend on. This despite the fact that it is possibly no longer validl.

If you need reliable validadition this may not be the appropriate approach.

(Example attached:) ask48657ValidityTree001.ods

edit flag offensive delete link more


Thanks for bringing that to the attention. However, I don't think it will become a problem.

LibreGuy gravatar imageLibreGuy ( 2015-04-02 21:20:57 +0100 )edit

Question Tools



Asked: 2015-04-02 15:31:37 +0100

Seen: 202 times

Last updated: Apr 02 '15