Newbie combobox ordeal.

[alex.k]

Hi,

Before i start let me tell you that I am completely new to Excel, so
please forgive if i am asking the obvious. Until today i thought that I
can manage most of the computer related stuff on my own, but as it
happens, i can't. I spent few hours browsing and searching on this
forum for an answer to my problem, i've seen a few that i thought will
work, but i wasn't able to make them work. [btw, english is not my
mothertongue, so give me some credit for mistakes ]

PROBLEM:
I am trying to construct a kind of calculator, which will be using
pre-defined values in one row, and performing calculations on them. I
want those values to be put in cells using combo box. I created the
worksheet [called DataT] with the column having names of the sets of
values, and values themselves, like this:
A B C D E
1 SET1 1 1 1 1
2 SET2 2 2 2 2
3 SET3 3 3 3 3

In the other worksheet, i have combobox, and have SET1,SET2 and so on
as names in a drop down list. And i would like the 4 cells next to the
combobox to fill in with the appropriate values from columns B to E, as
i pick the SET in the combobox. I have a general idea after reading many
posts that i need to use VLOOKUP to do this, but i am unable to
implement this function properly. I tried, but the best i achieved was
the number of the SET in the first cell next to the combobox. And i
would like to have, for example, when i pick SET2 in the drop-down
list, 2 2 2 2 in the four cells next to combobox. I think it is
possible, but I can't figure it on my own, so any help here will be
greatly appreciated. Thank you in advance.

Alex [the Confused]

 

[anilsolipuram]

let say in sheet you have

in 1st ,2nd ,3rd row you have:
set1 1 1 1 ' in A B C D CELLS
set2 2 2 2 ' in A B C D CELLS
set3 3 3 3 ' in A B C D CELLS

in sheet2 let say
cell a1 is combo box where you select the set1 or set 2 or set3

in b1 enter =VLOOKUP(A1,Sheet1!$A$1:$E$3,COLUMN(B1),0)
in C1 enter =VLOOKUP(A1,Sheet1!$A$1:$E$3,COLUMN(c1),0)
in D1 enter =VLOOKUP(A1,Sheet1!$A$1:$E$3,COLUMN(d1),0)
IN E1 enter =VLOOKUP(A1,Sheet1!$A$1:$E$3,COLUMN(e1),0)

 

[alex.k]

thank you for your time.
i have seen this solution here before and tried it but it doesn't work.
all i get is
a digit in a cell A1 to which combobox is linked [digit indicates the
position of the item chosen from the dropdown list in the box], and 4
errors in a cells where i put VLOOKUP formulas.

alex

 

[anilsolipuram]

check the attachment

sheet1 has data,

sheet2 has combo box at a1

change the value in a1 and the result will be b1,c1,d1,e1


+-------------------------------------------------------------------+
|Filename: combo_selection.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3539 |
+-------------------------------------------------------------------+

 

[alex.k]

thank you. i see it now. you used data validation, when i was tyring to
do this using form combobox. which i found out the way to do as well,
using INDEX.
thanks again.

 

[DCSwearingen]

How do you enter a combo box like you did in your zip file?

I know how to enter a combo box from the 'Forms' toolbox, but it does
not work like the one you attached.

 

[anilsolipuram]

if cells a2:a10 as data.

then the combo box can be created with data from a2:a10 by using
data->validation.

go to data->validation, select settings tab, for validation criteria
select allow : has list,and in the data enter =$a$1:$a$20, click ok .