I need to relate data in two cells to each other 1

[cubstyled]

I have created a list of names ( drop down cell) I need to place a related number in the cell next to it. I am not sure how to go about this. any help would be great! thanks!

For instance---
cell a10 is a drop down with the names Alex, Bill, Charlie
When I choose Alex I want B10 populated with a specific number ( like .25) that is somehow related to the name and if I choose Bill I want his coresponding number to show in the adjacent cell. I am doing this in excel because this information will be a part of usage and costing.

 

[AnotherHiggins]

How many names will you be using? (The max number you foresee needing.)

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[cubstyled]

I just found out from the VP who wants this, that it could be as many as a couple of hundred. Which poses another question
Can the list boxes in excel autofill or at least act inteligent. if i type a 'F' will it take me to the first F name and then if I type 'Fr' would it take me to Frank?
I may be going about this the wrong way. Our VP will setup the static info and our floor manager will be entering in the dynamic info as we try to get our time and costing.

 

[AnotherHiggins]

You'll want to use the VLOOKUP function. This page has a pretty good description of how it works. The same page has info on dropdown lists just below the VLookup info.

Basically I would have your magic number beside the list of names in a separate sheet - perhaps a hidden sheet if you want to be fancy. Set up two different Named Ranges - 1 just for the names (probably in Column A) and a second for the Names and the corresponding numbers (Columns A & B). You can then refer to these Named Ranges in the dropdown and the VLookup, respectively.

Post back if you need help.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[Zathras]

I would not use a drop-down cell. I would use a combo box from the Control Toolbox toolbar. (View / Toolbars / Control Toolbox)

1. Set up a test worksheet this way:
[tt]
F1: Name
F2: Alex
F3: Bill
F4: Charlie
G1: Percent
G2: .25
G3: .27
G4: .24
B10: =VLOOKUP(A10,$F$2:$G$4,2,0)
[/tt]
2. Drop a combo box on the form near A2 (or wherever you like)

3. Set the following properties for the combo box:[tt]
ColumnCount = 2
ColumnHeads = True
LinkedCell = A10
ListFillRange = F2:G4
MatchRequired = True
Style = 2 - fmStyleDropDownList
[/tt]
4. Exit Design Mode by clicking the appropriate button on the Control Toolbox toolbar.

5. Work the combo box and see what happens.

6. Put your real data in columns F and G (or wherever) and adjust the ListFillRange property accordingly.

 

[cubstyled]

Because of the size of the list it looks like that the combobox is the way i have to go. I there a way that the combo box can fill the cell with focus or does it have to be done for each row,In other words the props. for the combo has a linked cell of A10, do i have to set a combo for all rows ( b11, b12, etc...) or can it be set for the cell with focus?
and can i use a named range in another sheet instead for the vlookup and what would be the list fill range in the props. of the combo?
Aslo lets get a little deeper. To shrink the amount of names in the list can i create a combo that is a group and if that group is selected then the next combo will only have the related names?

Thanks again for all who have helped and all that will help

 

[Zathras]

You can do that using the data validation/list technique. Try this setup:
[tt]
F1: Marketing
F2: Sales
F3: Accounting
F4: Administration
I1: Alex
I2: Barbara
I3: Charles
J1: Dennis
J2: Earl
J3: Frank
K1: George
K2: Henry
K3: Inez
L1: Jerry
L2: Karl
L3: Larry
[/tt]
Assign range names: (Easiest way is to select cells and then type a name in the "Name Box" on the tool bar.)
Select F1:F4 and give it the name Departments
Select I1:I3 and give it the name Marketing
Select J1:J3 and give it the name Sales
Select K1:K3 and give it the name Accounting
Select L1:L3 and give it the name Administration

Select Column "A" and choose Data/Validation... from the menu. Set "Allow" to "List" and put =Departments in the "Source" box and click OK.

Select Column "B" and choose Data/Validation... from the menu. Set "Allow" to "List" and pub =INDIRECT(A1) in the "Source" box and click OK.

Choose a cell in column A and pick a department.
Choose a cell in column B and see that only employeees in that department are offered.

Alternative...
What version of Excel are you using?
If you have 2000 or later, and you want to learn about VBA, it is possible to set up a user form that is "modeless" which would allow you to select a cell on the worksheet and then do your combo box dance on the form.

 

[cubstyled]

I am using 2k, the list boxes worked great for validating agianst one another but with adding the rates in I couldn't figure out how to get them to validate agianst the individuals in marketing and so on. I'm not sure how to go about this now that it's not just two columns.
I am not a programmer (some understanding, very little) If the VBA would be a good newbie solution, i am willing to try anything at this point.

Thanks again!!

 

[Zathras]

Sorry, I forgot about the rates bit. Do this with what you have from my previous post:

1. Cut the names from J1:J3 and paste then in I4, and similarly cut and paste from K and L into I7 and I10.

2. Assign the range I1:J12 the name NamesAndRates.

3. Put your rates in column I next to the names.

4. Put this formula in C1 and copy down:
[tt]
=IF(ISNA(VLOOKUP(B1,NamesAndRates,2,0)),"",VLOOKUP(B1,NamesAndRates,2,0))
[/tt]
BTW, after you get it set up, you can cut and paste the named ranges to another sheet if you wish, and the formulas and validation will still work.

 

[cubstyled]

I just get #NAME? In C1 I take it when I cut and pasted the Defined names went with them, what happens if there is a same name and different rate? There shouldn't be if the defined names for a cell range still works ( i4:i6 contains bill defined as marketing and i10:i13 contains bill and a different rate defined as accounting)? Can you explain what #4 bit of code does?

thanks

 

[Zathras]

The names need to be unique in order for VLOOKUP to give you the results you want.

Perhaps you can use first and last names? I.e, Bill Smith and Bill Jones?

It is possible to set it up with separate lists by department (using more range names and slightly more complex formulas) but what if you have two Bill's in the same department? You will still have the same problem.

Keeping the ranges updated becomes more complicated as you add/delete names from the lists.

 

[cubstyled]

I forgot an "s" in names so ignore the error but I would like to know the breakdown of the command you had me paste

thanks

 

[Zathras]

The best way might be for you to lookup VLOOKUP and IF functions in the help file.

VLOOKUP uses the thing referenced in the first argument and scans the first column of the given array. The last argument (0 or FALSE) says that there must be an exact match. The third argument (2 in this case) says to take the data from the 2nd column in the given array (where the rates are).

If the thing referenced is not found in the first column of the range then VLOOKUP returns #N/A. That is handled by the first argument of the IF statement which says if the result of the VLOOKUP is #N/A, then display an empty cell, otherwise display the result of the VLOOKUP.

 

[cubstyled]

Thanks so much for all your Help