[Calc] interactive drop-down?

[LibreGuy]

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   
1  CATEGORY   OPTION
2  color      blue
3  size       small

Can this be done?

[JohnSUN]

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

[karolus]

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

[Regina]

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.

[LibreGuy]

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]

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?

[karolus]

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

[LibreGuy]

Allright, cool. Many thanks!

[Lupp]

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

[LibreGuy]

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