Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 02 Apr 2015 21:25:45 +0200[Calc] interactive drop-down?https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/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?Thu, 02 Apr 2015 15:31:37 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/Comment by JohnSUN for <p>Hi guys,</p>
<p>I'm not sure if what I want is possible, but maybe anyone of you can tell me if it is.</p>
<p>Lets say in Calc we create 2 tabs. In the second tab I would like to create "categories" with options, for example:</p>
<pre><code> A B
1 color size
2 red small
3 blue medium
4 green large
</code></pre>
<p>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:</p>
<pre><code> A B
1 CATEGORY OPTION
2 color blue
3 size small
</code></pre>
<p>Can this be done?</p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48659#post-id-48659May be you mean =VLOOKUP($B$2;$Sheet1.$A$2:$B$4;2;0)?Thu, 02 Apr 2015 16:34:45 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48659#post-id-48659Answer by LibreGuy for <p>Hi guys,</p>
<p>I'm not sure if what I want is possible, but maybe anyone of you can tell me if it is.</p>
<p>Lets say in Calc we create 2 tabs. In the second tab I would like to create "categories" with options, for example:</p>
<pre><code> A B
1 color size
2 red small
3 blue medium
4 green large
</code></pre>
<p>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:</p>
<pre><code> A B
1 CATEGORY OPTION
2 color blue
3 size small
</code></pre>
<p>Can this be done?</p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?answer=48662#post-id-48662Holy 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?Thu, 02 Apr 2015 17:00:56 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?answer=48662#post-id-48662Comment by LibreGuy for <p>Holy crap Karolus, that is very nice!!!</p>
<p>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?</p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48665#post-id-48665Allright, cool. Many thanks!Thu, 02 Apr 2015 17:55:41 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48665#post-id-48665Comment by karolus for <p>Holy crap Karolus, that is very nice!!!</p>
<p>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?</p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48664#post-id-48664Creating 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 additionalThu, 02 Apr 2015 17:50:25 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48664#post-id-48664Answer by Regina for <p>Hi guys,</p>
<p>I'm not sure if what I want is possible, but maybe anyone of you can tell me if it is.</p>
<p>Lets say in Calc we create 2 tabs. In the second tab I would like to create "categories" with options, for example:</p>
<pre><code> A B
1 color size
2 red small
3 blue medium
4 green large
</code></pre>
<p>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:</p>
<pre><code> A B
1 CATEGORY OPTION
2 color blue
3 size small
</code></pre>
<p>Can this be done?</p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?answer=48668#post-id-48668You 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](/upfiles/1427990869198450.ods))<br>
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.<br>
The formula for validity in B2 is INDEX(myItems;0;MATCH(A2;myCategory;0))<br>
When you add or remove categories or items, you only need to adapt the information in the definition of the names.
Thu, 02 Apr 2015 18:12:47 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?answer=48668#post-id-48668Comment by LibreGuy for <p>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.</p>
<p>For example (see attached <a href="/upfiles/1427990869198450.ods">file</a>)<br/>
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.</p>
<p>The formula for validity in A2 is myCategory.<br/>
The formula for validity in B2 is INDEX(myItems;0;MATCH(A2;myCategory;0))<br/>
When you add or remove categories or items, you only need to adapt the information in the definition of the names.</p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48675#post-id-48675This 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!Thu, 02 Apr 2015 21:25:45 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48675#post-id-48675Answer by Lupp for <p>Hi guys,</p>
<p>I'm not sure if what I want is possible, but maybe anyone of you can tell me if it is.</p>
<p>Lets say in Calc we create 2 tabs. In the second tab I would like to create "categories" with options, for example:</p>
<pre><code> A B
1 color size
2 red small
3 blue medium
4 green large
</code></pre>
<p>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:</p>
<pre><code> A B
1 CATEGORY OPTION
2 color blue
3 size small
</code></pre>
<p>Can this be done?</p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?answer=48669#post-id-48669You 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](/upfiles/142799377261012.ods)
Thu, 02 Apr 2015 18:56:32 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?answer=48669#post-id-48669Comment by LibreGuy for <p>You get very high flexibiity defining calculated validity ranges with the help of OFFSET.
<strong>Any validation depending on the content of some other cells is logically doubtable,</strong> 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. </p>
<p><strong>If you need reliable validadition this may not be the appropriate approach.</strong></p>
<p>(Example attached:) <a href="/upfiles/142799377261012.ods">ask48657ValidityTree001.ods</a></p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48674#post-id-48674Thanks for bringing that to the attention. However, I don't think it will become a problem.Thu, 02 Apr 2015 21:20:57 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?comment=48674#post-id-48674Answer by karolus for <p>Hi guys,</p>
<p>I'm not sure if what I want is possible, but maybe anyone of you can tell me if it is.</p>
<p>Lets say in Calc we create 2 tabs. In the second tab I would like to create "categories" with options, for example:</p>
<pre><code> A B
1 color size
2 red small
3 blue medium
4 green large
</code></pre>
<p>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:</p>
<pre><code> A B
1 CATEGORY OPTION
2 color blue
3 size small
</code></pre>
<p>Can this be done?</p>
https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?answer=48661#post-id-48661select 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 ColumnThu, 02 Apr 2015 16:40:11 +0200https://ask.libreoffice.org/en/question/48657/calc-interactive-drop-down/?answer=48661#post-id-48661