Hello Guys i can sure appreciate your help on this one.

i developing an application in excell, the problem is that i dont necesserally like the thing so i didn't knew that it can do so many things... so here is the drill down


i have several columns, lets call them A, B, C,
column A is difficulty like High, Medium, Low
column B is Hrs to work, 80 for High, 40 for Medium, and 20 for low
column C is Concatenation of A,B

what i want to do is in Column A do a simple Validation i have already done this... the tricky part comes in colum B, this spreadsheet goes 120 rows deep, and can potentially grow even bigger, so i want to auto populate the number in column B when you hit the correct High, Medium, Low option,

what i thought at first well maybe i can do a simple match like so:
=INDEX($AQ$4:$AR$6,MATCH("High",$AQ$4:$AQ$6,),MATC H(80,$AQ$4:$AR$4,)) INDEX($AQ$4:$AR$6,MATCH("Medium",$AQ$4:$AQ$6,),MAT CH(40,$AQ$5:$AR$5,)) INDEX($AQ$4:$AR$6,MATCH("Low",$AQ$4:$AQ$6,),MATCH( 20,$AQ$6:$AR$6,))

but the darn think will not make it happen for me..

can anyone help, suggest what the heck is going on here?
thnxs very much in advanced

Hi,

It would seem that some simple If statements would do the job, but the formula you have shown seems to be looking for something far more than this.

However here is how I would go about it:

=IF(A2<>"",IF(A2="High",80,IF(A2="Medium",40,IF(A2 ="Low",20,"Error"))),"")

Put this in B2 with the "High, Medium, Low in A2 and B2 should give you the answer you want. The error would show up for values outside the criteria and the if A2<>"" will give a nul value if nothing is there to be examined.

Is this what you were after?

Mike

Howdy. Actually it is not simple. You need to use dependent lists.

When you say "120 rows deep" do you mean any item in column A might have 120 items associated with it?

Let's say that you have the three items in Column A on worksheet "Work". Then on another worksheet (name it "Control"), set up three named ranges (one for each item in Col A).

So on Control worksheet, in Cell B1, put "High" (without quotation marks). Then in cells B2:B5 (or however many you need), add the items related to High. Now in Cell C1, put "Medium", and in C2:C8 add the items related to "Medium". And in D1 put "Low" and in D2: D12 add items related to "Low". I would add a fourth named range while you are at it: in Cell A1 put the name Grade, then in A2 put High, in A3 put Medium, and in A4, put Low.

Now select the range B2:B5 (not including the header), and name it High. Select C2:C8 and name it Medium. Select D2: D12 and name it Low. Select A2:A4 and name it "Grade".

Now go back to worksheet Work.

Say that you have A3 as the first cell, go to Data > Validation, and in the dropdown choose List, and in "Refers to" type:

=Grade

then select cell B3, go to Data > Validation and from the dropdown list, choose List. Then in the "refers to" box type:

=INDIRECT(A3)

Here is an example of the setup described above.

Actually both of you guys are correct... that was the second step to get the first formula into the correct row and column and the get the second validation trugh another validation...
this will make life much easier now... thank you both for your input i sincerely appreciate it