data sort of like

M

Mig

Hi experts

I like to have a tabel like this



This is one 1
This is two 2
This is someother 5
Text 4



that say a text that is assosiated with a number. Then (some where else in
the spreedsheet) I like to have a drop down wther I can choose between
("This is one"; "This is two", "This is..."), and depending on the choosen
text I will use the assosiated number in some calculations. Is this possible
with Excel?

e.g. if in the drop down is in cell A5, and there is choosen "This is Two",
and in cell A4 says 55 like to write some thing like this, in cell A6:
"=A5*A4" and the the result should be 110

Is this possible with Excel?

best regards
Torben
 
F

Frank Kabel

Hi Torben
Try the following

1. your table with the possible options is in columns D1:E4 (for more
options enlarge this area), e.g.
D E
1 This is one 1
2 This is two 2
3 ...
4 ...
Note: I have separated your text and the corresponding values in two
columns

2. Highlight A5, goto 'Data - Validation', choose list and select the
area D1:D4

3. In cell A6 enter the following
=IF(AND(A4<>"",A5<>""),A4*VLOOKUP(A5,$D:$1:$E:$4,2,FALSE),"")
The first part checks if A4 and A5 are not blank


HTH
Frank
 
M

Max

One way using Data Validation and VLOOKUP

Assume your sample table below is in A1:B3
(text in A1:A3, numbers in B1:B3)
This is one 1
This is two 2
This is someother 5

Name the range A1:A3 as say: MyList
Name the range A1:B3 as say: MyTable

You have in A4: 55

Select A5
Click Data > Validation > Settings tab
Under "Allow:" select "List" from dropdown
Under "Source:" Put: =MyList
Click OK

(This sets up the Data Validation in A5, you'll get a dropdown list to
select)

Put in A6: =VLOOKUP(A5,MyTable,2,0)*A4

If A5 = "This is two", A6 will return 110 (i.e. 2 x 55)

The above should give you what you're after
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top