none
index based selectionlist, translatable RRS feed

  • Question

  • I have the following problem:

    In an Excelsheet I defined a column where I want to allow only some predefined textural input. Let's say for example "Morning" "Noon" and "In the evening". Furthermore I need the choosen value for further calculation in my Add-In.

    To make all this complicated. The elements of my selectionlist must be translatable.

    And it would be a great benefit, If the user could choose one of the texts from a Dropdown list.

    An optimal solution would be, to store only the index of the list in the cell, but display the text. (Further calculation would be simple in that case.)

    So only by redefining the list in a different language would then show the translated texts, without changing the content of the cells, (which is the index.)

    Does anybody have a clue, how to achieve this?

    Some further Information:

    I already played arround with the cellformat:

    It is possible to make a definition like: [=0]"Morning";[=1]"Noon"

    This is what I want, but whenever I define a third option, an errormessage occurs.

    Wednesday, January 21, 2015 12:17 PM

Answers

  • Perhaps Microsoft Access would be more suitable for what you want.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by shawnzkz Tuesday, February 3, 2015 2:56 PM
    • Marked as answer by CaillenModerator Tuesday, February 3, 2015 2:57 PM
    Wednesday, January 21, 2015 5:10 PM
  • Create a list of the text options in a range, e.g. K1:K3.

    If you don't see the Developer tab of the ribbon, select File > Options, click Customize Ribbon, and tick the check box for Developer in the list of Main Tabs on the right hand size, then click OK.

    On the Developer tab, in the Controls group, click Insert > Combo Box (Form Control).

    Drag a rectangle on the sheet. This will insert a combo box and leave it selected.

    Right-click the combo box and select Format Control... from the context menu.

    Click in the Input Range box and enter the address of the list of options, or select the list range with the mouse.

    Click in the Cell Link box and enter the address of the cell where you want to enter a value, or select that cell with the mouse.

    Click OK.

    Click outside the combo box. You can now use the combo box to select an item.

    The Cell Link will contain the 1-based index of the selected item. I.e. if you select the 3rd item, the cell link will contain 3.

    To edit the combo box, right-click it. You can then select an item from the context menu, or move the combo box, etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 21, 2015 4:03 PM

All replies

  • Create a list of the text options in a range, e.g. K1:K3.

    If you don't see the Developer tab of the ribbon, select File > Options, click Customize Ribbon, and tick the check box for Developer in the list of Main Tabs on the right hand size, then click OK.

    On the Developer tab, in the Controls group, click Insert > Combo Box (Form Control).

    Drag a rectangle on the sheet. This will insert a combo box and leave it selected.

    Right-click the combo box and select Format Control... from the context menu.

    Click in the Input Range box and enter the address of the list of options, or select the list range with the mouse.

    Click in the Cell Link box and enter the address of the cell where you want to enter a value, or select that cell with the mouse.

    Click OK.

    Click outside the combo box. You can now use the combo box to select an item.

    The Cell Link will contain the 1-based index of the selected item. I.e. if you select the 3rd item, the cell link will contain 3.

    To edit the combo box, right-click it. You can then select an item from the context menu, or move the combo box, etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 21, 2015 4:03 PM
  • Hi Hans,

    interesting post. It almost fulfills my needs. Except that I have a whole column. In fact it is one column of a ListObject. (This is the item that you can define under Insert/Tables/Table. I hope I translated this correctly from the German Excel Version)

    The list can grow by typing another value right below its bottom. Doing this, the new column-cell shall have the same functionality concerning the sellection.

    Right now, all cells in the column use a Data/Datavalidation/List. But this list can not be defined using the same indirection that you show in your post. Instead the text itself is inserted into the cell. So there is no posibility to change the selected text if the user changes the language.

    Thank's a lot for your post. Regards, Helmut

    Wednesday, January 21, 2015 4:56 PM
  • Perhaps Microsoft Access would be more suitable for what you want.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by shawnzkz Tuesday, February 3, 2015 2:56 PM
    • Marked as answer by CaillenModerator Tuesday, February 3, 2015 2:57 PM
    Wednesday, January 21, 2015 5:10 PM