drop down for excel?

[Guest]

how do i assign 7 different sale types to have 7 different percentage values

 

[Nick Hodge]

Mike

Your question is very vague. If you have a combobox from the 'forms'
toolbar then you can set up a lookup table in a hidden sheet and then use
the index of the selection to look it up using the INDEX function.

Perhaps post back with some more information on what you currently have and
I'm sure someone will help.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Bob Phillips]

Sounds that you want a table of types in one column, percentages in another,
then use VLOOKUP to lookup the text into that table, like

=VLOOKUP(type,lookup_table,2,False)

--

HTH

RP
(remove nothere from the email address if mailing direct)

how do i assign 7 different sale types to have 7 different percentage

values

 

[Guest]

ok i have 4 sales people and they can sell an Item 7 different ways. all 7
have different % of commmission. I want to be able to have a drop down box
that lets me choose which sale type it is and it will automatically put in
the percentage of the sale type

ex Bob sells item a this way and gets 10%---if he sell it this way he gets
15% etc..

 

[Nick Hodge]

Mike

(Scenario: Drop downs on Sheet1 range A2:A500, lookup table for 7 different
types of commission on Sheet2 Range A2:B8. Commission description in A2:A8
and the commission value in B2:B8. Change for you scenario)

I would use data validation

Select the range for the drop-downs and go to Data>Validation. Select
'list' from the dropdown and in the source box type your commission types
separated by commas.

Go to Sheet2 and in A2:A8 enter the same descriptions for the commissions,
exactly as you have in the data validation box and then in Sheet2 range
B2:B8 enter the corresponding commission values

In my scenario in Sheet1 B2 enter

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE)),"",VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE))

You could use a single VLOOKUP but the ISERROR places a blank if there is
nothing selected in the dropdown to the left.

Now drag this formula down to B500 (in my scenario) and now when you select
a value from a dropdown in columnA the value should appear alongside

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)