Change number based on specific cell with number

[Edd Chia]

How do i change a cell with number based on selected specific dropdown cell with number. Example, when i input a number in 'A1' 100, 'B1' automatic change to 125 and when i input another number in the same cell 'A1' 99, 'B1' auto change to 120. kindly assist me please.

Cell A Cell B
A1= 100 B1= 125
A1= 99 B1= 120
A1= 98 B1= 115
A1= 97 B1= 110
A1= 96 B1= 105
A1= 95 B1= 100
A1= 94 B1= 95

 

[villy]

You can use this formula
[B1]=INDEX($A$34:$B$40,MATCH(A1,$A$34:$A$40,0),2)

note: A1 is a data validation list with range $A$34:$B$40


change ranges to suit

 

[Marcelo Branco]

Hi,

Considering
A1 = 100
B1 = 125
as a starting point, if B1 decreases by 5 units for each unit decreased in A1 (or increases 5 units for each unit incresead in A1), maybe

B1
=125-(100-A1)*5

M.

 

[villy]

Marcelo,
If A1 = 99 B1 should be 120 as per Op
yours is B1 = 130

 

[villy]

As per Marcelo,
B1 =125 + (A1-100)*5
Only if 125 is a fixed one.

 

[Marcelo Branco]

Marcelo,
If A1 = 99 B1 should be 120 as per Op
yours is B1 = 130

Villy,

I corrected my formula. I made a mistake in my post (typo), but later i edited it.

Anyway, tks for noticing and correcting

M

 

[Edd Chia]

Thanks for the update guys. however i didnt get it. Can you guys guide me on inserting the formula i prepared? i really need this.
<TABLE style="WIDTH: 321pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=426><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" span=2 width=122><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: black; WIDTH: 184pt; HEIGHT: 15pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" id=td_post_2814461 class=xl65 height=20 width=244 colSpan=2>Average QD Score</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; WIDTH: 29pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 width=38> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; WIDTH: 60pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 width=80> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: white; BORDER-RIGHT: white 0.5pt solid" class=xl64 height=20>Target</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white; BACKGROUND-COLOR: black; BORDER-TOP: white; BORDER-RIGHT: white 0.5pt solid" class=xl64>Target Met</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>Target</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>Target Met</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: white; BORDER-RIGHT: white 0.5pt solid" class=xl64 height=20>100</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white; BACKGROUND-COLOR: black; WIDTH: 92pt; BORDER-TOP: white; BORDER-RIGHT: white 0.5pt solid" class=xl67 width=122>125

</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" id=td_post_2814461 class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>100</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>125</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>99</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>120</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>98</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>115</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>97</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>110</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>96</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>105</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>95</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>100</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>94</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>75</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>93</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>70</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>92</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>65</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>91</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>90</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>55</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>89</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>88</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63> </TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>87</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>0</TD></TR></TBODY></TABLE>

 

[Edd Chia]

Thanks for the update guys. however i didnt get it. Can you guys guide me on inserting the formula i prepared? i really need this.. i want the 'Target' cell to have dropdown listed (in 1 cell) and 'Target Met' cell changed according to 'Target' cell as listed (in 1 cell). can anyone help

<TABLE style="WIDTH: 321pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=426><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" span=2 width=122><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: black; WIDTH: 184pt; HEIGHT: 15pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" id=td_post_2814461 class=xl65 height=20 width=244 colSpan=2>Average QD Score</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; WIDTH: 29pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 width=38></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 width=64></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; WIDTH: 60pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 width=80></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: white; BORDER-RIGHT: white 0.5pt solid" class=xl64 height=20>Target</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white; BACKGROUND-COLOR: black; BORDER-TOP: white; BORDER-RIGHT: white 0.5pt solid" class=xl64>Target Met</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>Target</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>Target Met</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: white; BORDER-RIGHT: white 0.5pt solid" class=xl64 height=20>100</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white; BACKGROUND-COLOR: black; WIDTH: 92pt; BORDER-TOP: white; BORDER-RIGHT: white 0.5pt solid" class=xl67 width=122>125


</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" id=td_post_2814461 class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>100</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>125</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>99</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>120</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>98</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>115</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>97</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>110</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>96</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>105</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>95</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>100</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>94</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>75</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>93</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>70</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>92</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>65</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>91</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>90</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>55</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>89</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>88</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; HEIGHT: 15pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>87</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: black; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl63>0</TD></TR></TBODY></TABLE>

 

[villy]

First you try to put the following values in desired Rows (can be on another sheet then hide it if you don't want to show) If you will use my formula
In B1 copy paste this formula:
[B1]=INDEX(Sheet2!$A$1:$B$20,MATCH(A1,Sheet2!$A$1:$A$20,0),2)

note: A1 is a data validation list with range $A$1:$B$20 in sheet2
In A1 of Sheet1..
GoTo Data|Validation then in Allow field choose list then choose the range of sheet2 A1:A20

 

[Edd Chia]

Ok. it still wont work when i copy paste the formula given. ok lets make it simple so i can get the picture of it. i have made the drop down in A1 but when i copy the formula in B1 it will popup a error msg box. however i could not make a data validation list with 2 column. below simple value. if this can work i will know how to do it.

A1 B1
100 125
99 120