Assigning values to different entries

[Calculon]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I would like to be able to create lists with pull-down style tabs where I could pick an entry an that entry would be equal to a different numerical value.

For instance, says I have the following list:

AB
CD
EF
GH

In a given cell, I put the option of selecting one entry in the above list (that I know how to do). But I would like each list entry to equate a numerical value. If I select AB the cell value should be 1, CD = 2, etc. I want to be able to make several pull-down lists and assign a numerical value to the entry chosen and later on add them together.

I know it can be done I see this all the time.

 

[Laroche J]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I would like to be able to create lists with pull-down style tabs where I
could pick an entry an that entry would be equal to a different numerical
value.

For instance, says I have the following list:

AB
CD
EF
GH

In a given cell, I put the option of selecting one entry in the above list
(that I know how to do). But I would like each list entry to equate a
numerical value. If I select AB the cell value should be 1, CD = 2, etc. I
want to be able to make several pull-down lists and assign a numerical value
to the entry chosen and later on add them together.

I know it can be done I see this all the time.

If you know how to do a pull-down list, you certainly already defined the
Cell link, a cell containing the rank of the selection in your list. Now,
use this value (the rank) in the INDEX function, to pinpoint the matching
numerical value in the values list (which, for clarity, should be beside the
list used in the pull-down)

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 12.1.7

 

[Shane Devenshire]

Hi,

I suggest a little lookup table such as

AB 2
CD 5
EF 1
GH 3

Suppose this is in M1:N4 and your dropdown is in A1 then in B1 enter the
following formula

=IF(A1<>"",VLOOKUP(A1,$M$1:$N$4,2,FALSE)

Of course if there were only 4 values you could also use a nested IF without
the need for a lookup table, but I prefer lookup tables, they're far more
flexible.

 

[Calculon]

Thank you that was exactly what I was looking for.

 

[Calculon]

Now, what if I introduce a variant; for instance:

A B C
1 Apple $1 Red
2 Pear $2 Green
3 Grapes $3 Purple

Say I place a drop-down menu in A4 where I can choose between Apple, Pear and Grapes. In B4 the amount in $ will appear, exactly as what I requested earlier. But if I would like a description to be added in C4, how would I go about achieving this? So if I select 'Pear' in the drop-down menu in A4, '2$' will appear in B4 and I would like 'Green' to appear in C4.

Thanks.

 

[Bob Greenblatt]

Now, what if I introduce a variant; for instance:

A B C
1 Apple $1 Red
2 Pear $2 Green
3 Grapes $3 Purple

Say I place a drop-down menu in A4 where I can choose between Apple, Pear and
Grapes. In B4 the amount in $ will appear, exactly as what I requested
earlier. But if I would like a description to be added in C4, how would I go
about achieving this? So if I select 'Pear' in the drop-down menu in A4, '2$'
will appear in B4 and I would like 'Green' to appear in C4.

Thanks.

Do you want to use data validation for the list, or a list control form the
forms tool bar. If the latter, it will return an "index" or pointer into the
list. In the other cells you can use the index function to select the
appropriate item from the other lists in columns B and C.

 

[Calculon]

I'm afraid I'm not sure I'm following this.

What would be the index formula required?

Thanks.

 

[Laroche J]

Now, what if I introduce a variant; for instance:

A B C
1 Apple $1 Red
2 Pear $2 Green
3 Grapes $3 Purple

Say I place a drop-down menu in A4 where I can choose between Apple, Pear and
Grapes. In B4 the amount in $ will appear, exactly as what I requested
earlier. But if I would like a description to be added in C4, how would I go
about achieving this? So if I select 'Pear' in the drop-down menu in A4, '2$'
will appear in B4 and I would like 'Green' to appear in C4.

Thanks.

There are two ways to create a drop-down: with a Combo Box or with Data
validation. In both instances the source or input range would be A1:C3.

With a Combo Box from the Forms toolbar, you'll be able to define a Cell
link, that will give you the rank of the selection. If the Cell link is A4,
then in C4 you'd then use =INDEX($A1:$C3,A4,3)

With Data / Validation (Allow: List) in A4, the formula for the color in C4
would be =VLOOKUP(A4,$A1:$C3,3,FALSE)

In both formulas, the "3" you see tells Excel to use the third column of the
range to report the value. If your table had 10 columns you could use for
different properties a similar construct and just change the column number
from which you fetch your data.

If you use the formula builder to create your formula (i.e. you type = then
click fx in the formula bar to select a function from the list that appears)
each argument will be explained on-the-fly.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 12.1.latest