Excel rookie need help with drop down lists
Author Message
Excel rookie need help with drop down lists

I have a spreadsheet where I have 3 columns, each with 3 choices of data - I
have validated these so you have a drop down choice when selecting the cell.
What I want to do is create a numeric value dependant on the choices made in
the three columns and then take that value & use it in a formula to achieve
a further result - I have the values (27 I think!) I just do not know how to
get the spreadsheet to come up with the answer after a choice has been made.

Does that make sense?

Any help would be greatly appreciated.

Cheers

Fec

Sat, 29 Oct 2005 20:46:30 GMT
Excel rookie need help with drop down lists
It sounds as if you'd need to use a lookup table to interpret your letter
choice as a number.

If you had A through D in C2:C5 and 1 through 4 in D2:D5, that would be your
table.

Make an entry in A2,  and in B2 use =VLOOKUP(A2,C2:D5,2).

--
Greeting from the Mississippi Gulf Coast!
http://myweb.cableone.net/twodays

Quote:
> I have a spreadsheet where I have 3 columns, each with 3 choices of data -
I
> have validated these so you have a drop down choice when selecting the
cell.
> What I want to do is create a numeric value dependant on the choices made
in
> the three columns and then take that value & use it in a formula to
achieve
> a further result - I have the values (27 I think!) I just do not know how
to
> get the spreadsheet to come up with the answer after a choice has been
made.

> Does that make sense?

> Any help would be greatly appreciated.

> Cheers

> Fec

Sat, 29 Oct 2005 20:59:27 GMT
Excel rookie need help with drop down lists
Create a vlookup table (could be done hardcoded  as well), lets
say your dropdown value is in B1

=IF(B1="","",VLOOKUP(B1,Table,2,0)

for simplicity in the explanation the dropdown values
are a, b, c, the values are  20, 30, 40 then it would look like

=IF(B1="","",VLOOKUP(B1,{"a",20;"b",30;"c",40},2,0))

so if you select "c" from the dropdown the formula will return 40

--

Regards,

Peo Sjoblom

Quote:
> I have a spreadsheet where I have 3 columns, each with 3 choices of data -
I
> have validated these so you have a drop down choice when selecting the
cell.
> What I want to do is create a numeric value dependant on the choices made
in
> the three columns and then take that value & use it in a formula to
achieve
> a further result - I have the values (27 I think!) I just do not know how
to
> get the spreadsheet to come up with the answer after a choice has been
made.

> Does that make sense?

> Any help would be greatly appreciated.

> Cheers

> Fec

Sat, 29 Oct 2005 21:01:44 GMT
Excel rookie need help with drop down lists
Use VLOOKUP. Create a table with one column of all combinations, and another
of the associated values, then use

=VLOOOKUP(value1&value2&value3, lookup table, 2, False)

make sure that the column of values is text, as concatenating values outputs
as text.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

Quote:
> I have a spreadsheet where I have 3 columns, each with 3 choices of data -
I
> have validated these so you have a drop down choice when selecting the
cell.
> What I want to do is create a numeric value dependant on the choices made
in
> the three columns and then take that value & use it in a formula to
achieve
> a further result - I have the values (27 I think!) I just do not know how
to
> get the spreadsheet to come up with the answer after a choice has been
made.

> Does that make sense?

> Any help would be greatly appreciated.

> Cheers

> Fec

Sat, 29 Oct 2005 21:02:43 GMT
Excel rookie need help with drop down lists
The method you need to use will depend on how the values relate to the
choices, and what the values/choices are.  Someone (possibly me) may be able
to provide with a single formula to calculate your values from the choices.

If there is no way to formulate your final values directly from the choices,
you may have to produce a table, and lookup the value using something along
the lines of:

=index(values,match(1,
(choices1=choice1)*(choices2=choice2)*(choices3=choice3),0))

Provide the choices and values, for us to play with...

Steve D.

Quote:
> I have a spreadsheet where I have 3 columns, each with 3 choices of data -
I
> have validated these so you have a drop down choice when selecting the
cell.
> What I want to do is create a numeric value dependant on the choices made
in
> the three columns and then take that value & use it in a formula to
achieve
> a further result - I have the values (27 I think!) I just do not know how
to
> get the spreadsheet to come up with the answer after a choice has been
made.

> Does that make sense?

> Any help would be greatly appreciated.

> Cheers

> Fec

Sat, 29 Oct 2005 21:15:54 GMT

 Page 1 of 1 [ 5 post ]

Relevant Pages

Powered by phpBB® Forum Software © phpBB Group