drop down on text and numeric based

[xljgd]

Hello All,


I have a drop down list box that has say 1,2,3 values

On selection

[pre]

A           B     c
1         3      a1*b1
3         1      a2*b2
2         1      a3*b3

[/pre]
The user has to select 3 option say high – red, med- yellow, low – green. Ideally I have to have a drop down box with the 3 options and the user selects the option and based on conditional formatting the color changes.


The problem that I am facing is how to have the scores(in col c) as well as the high, med, low text to mean the same thing.

Eg. In A & B column if user selects 1 i.e low, 2 i.e med, 3, i.e high. The drop down should display the text but the value in the cell should be numeric for me to get the value calculated in C column based on A & B selection. Also in the end I need to count the total number of red, green or yellow based on the column c.

Any pointers on how I should proceed would be greatly appreciated.


thanks once again

 

[Istiyak]

hi xljgd


Kindly dowload file from my website and check.


http://istiyakshaikh.hpage.com > Downloads > For_Solution_xljgd.xls


Plz do conditional formating as per your requirement or tell.


Regards

Istiyak

 

[xljgd]

Thanks for your prompt reply. i downloaded your worksheet and it is in line with what i need. can you explain the logic.


for me the

[pre]

high = 3
Med = 2
Low = 1

h x h = 9
m x m = 4
L * L = 1

L * h = 3

[/pre]
and so on. also on the countif i have to count the number of high, low and med in col a and b so i included both =COUNTIF($A$2:$B$13,"LOW") to acheive this and than i have to set a CF. currently your sheet is the reverse of what i want how to change it. thanks for your awesome site and inputs.

 

[xljgd]

Thanks Istiyak,


i changed the order of the match and the list order and got the result i needed. but when i list


low,med, high and match low med high it assumed that low = 1 med =2 and high =3 and does the muliplication by the postion of the list. is this correct.

can i do it for more than 3 values.


thanks for all your help your solution was what i needed.

 

[Istiyak]

Thanks


Good to hear you got what you need.


Obvsly u can change the part of formula.


Happy to help. Keep visit.


Regards

Istiyak

 

[xljgd]

I require some more direction to add on to Istiyak solution . I have to add a 4th element called complete.


L=1

M=2

H=3 and complete = 0 so that when i do complete x complete i will get 0 and there will be no score. how is this achieved. i tried to modify the formula but i am getting Complete assumed as 4 and it is giving me the multiples of 4. can anyone please help.


thanks

 

[NARAYANK991]

Hi ,


Replace your existing formulae in C2 downwards , with the following formula :


=(MATCH($A2,List_of_choices,0)-1)*(MATCH($B2,List_of_choices,0)-1)


Here , List_of_choices would be :


{"COMPLETE","LOW","MED","HIGH"}


Basically , if you entered List_of_choices as :


{"LOW","MED","HIGH","COMPLETE"}


then , selecting COMPLETE would make the MATCH function return 4. Putting COMPLETE at the head of the list makes MATCH return 1 ; the -1 ensures that you get 0.


Narayan

 

[xljgd]

Hello Narayanak


thanks again for your time.


I changed the formula but when i select complete * complete it gives me -1 and all my other selections are out of order too.


for eg. i have to get


low =1

med = 2

high = 3


so the multiples should be 2,4,9 but i am getting 2,5,11 etc. i have the list as loc


my formula is =(MATCH($T26,LOC,0)-1)*MATCH($V26,LOC,0)-1


also should the same order be followed in both columns.


thanks

 

[NARAYANK991]

Hi ,


I think there should be a parenthesis enclosing the second MATCH function ( which should include the -1 after it ) , just as it is there for the first. Just copy the entire formula from this post , and paste it in your worksheet and see if it works.


=(MATCH($T26,LOC,0)-1)*(MATCH($V26,LOC,0)-1)


Narayan

 

[xljgd]

the paranthesis was the problem. i should have tested it more before i emailed to the forum. thanks again. appreciate your time, patience and help with this.