mokaplanjr
Technical User
If this seems like a silly question, please don't yell at me. Just help me.
I want to make a list box in Excel that will display a series of labels to click on, but once clicked, delivers other values (obtained from various cells with equally various formulas.) Example:
A B
1 LABEL VALUE
2 Red Paint $100.00 (or a formula)
3 Blue Paint $75.00 "
4 Yellow Paint $60.00 "
And so forth. I can do it in a clunky way by creating a form list box that will deliver to a cell the values 1 through X (number of items in list) from a one-column range, then in another cell, use a rather non-user-friendly IF logic formula that pulls data from cells based on the value of the list selection. The more cells in the list range, the longer and clunkier it gets, let me tell you. It looks pretty and it works, but the more cells in the range, the longer and funkier the IF logic formula gets.
FYI: Here is the IF logic for JUST THREE values:
=IF(B2=1,PRICE_LIST!C11,(IF(B2=2,PRICE_LIST!C30,(IF(B2=3,PRICE_LIST!C31,"x"
))))
Cell B2 gets is values (1 - 3) from the list box selection.
Cells C11, C30, and C31 get values from either direct entry or from formulas. It works, but I can't imagine adding more choices this way.
I tried the ol' Data>Validation>Allow>List deal, but it will only display the range of values (named or otherwise) I select, not the text label I so desire. Also, if one of the values in the range is changed, it does not change what is visible in the in-cell dropdown immediately. You have to "refresh" it by clicking another value, then back again.
I want to get the visible, clickable label range from the "LABEL" column and the value range from the "VALUE" column. From a database, this is too easy with a SQL query; oh why, oh why is it so difficult in Excel? This should be at least as simple to do.
The only things I want that have the same values as the labels they display are members of Congress.
I want to make a list box in Excel that will display a series of labels to click on, but once clicked, delivers other values (obtained from various cells with equally various formulas.) Example:
A B
1 LABEL VALUE
2 Red Paint $100.00 (or a formula)
3 Blue Paint $75.00 "
4 Yellow Paint $60.00 "
And so forth. I can do it in a clunky way by creating a form list box that will deliver to a cell the values 1 through X (number of items in list) from a one-column range, then in another cell, use a rather non-user-friendly IF logic formula that pulls data from cells based on the value of the list selection. The more cells in the list range, the longer and clunkier it gets, let me tell you. It looks pretty and it works, but the more cells in the range, the longer and funkier the IF logic formula gets.
FYI: Here is the IF logic for JUST THREE values:
=IF(B2=1,PRICE_LIST!C11,(IF(B2=2,PRICE_LIST!C30,(IF(B2=3,PRICE_LIST!C31,"x"
))))Cell B2 gets is values (1 - 3) from the list box selection.
Cells C11, C30, and C31 get values from either direct entry or from formulas. It works, but I can't imagine adding more choices this way.
I tried the ol' Data>Validation>Allow>List deal, but it will only display the range of values (named or otherwise) I select, not the text label I so desire. Also, if one of the values in the range is changed, it does not change what is visible in the in-cell dropdown immediately. You have to "refresh" it by clicking another value, then back again.
I want to get the visible, clickable label range from the "LABEL" column and the value range from the "VALUE" column. From a database, this is too easy with a SQL query; oh why, oh why is it so difficult in Excel? This should be at least as simple to do.
The only things I want that have the same values as the labels they display are members of Congress.