OFFSET and MATCH Functions??

D

dan345

New Member
Joined
Mar 6, 2011
Messages
19
Hi,

can anyone help me with offset and match functions. basically if you have a look at the file i provided.

how do i get a drop down box that will list from a-g, and then when user selects say a, it will list down all the data from column a undernearth the coloumn named data source.

<table width="1088" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="17"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl65">a</td> <td class="xl65">b</td> <td class="xl65">c</td> <td class="xl65">d</td> <td class="xl65">e</td> <td class="xl65">f</td> <td class="xl65">g</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66" colspan="2" style="">data source</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">7</td> <td class="xl67">0.27</td> <td class="xl67">0.36</td> <td class="xl67">20.7</td> <td class="xl67">0.045</td> <td class="xl67">45</td> <td class="xl67">170</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66" colspan="2" style="">drop down</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.3</td> <td class="xl67">0.3</td> <td class="xl67">0.34</td> <td class="xl67">1.6</td> <td class="xl67">0.049</td> <td class="xl67">14</td> <td class="xl67">132</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">8.1</td> <td class="xl67">0.28</td> <td class="xl67">0.4</td> <td class="xl67">6.9</td> <td class="xl67">0.05</td> <td class="xl67">30</td> <td class="xl67">97</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">7.2</td> <td class="xl67">0.23</td> <td class="xl67">0.32</td> <td class="xl67">8.5</td> <td class="xl67">0.058</td> <td class="xl67">47</td> <td class="xl67">186</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">7.2</td> <td class="xl67">0.23</td> <td class="xl67">0.32</td> <td class="xl67">8.5</td> <td class="xl67">0.058</td> <td class="xl67">47</td> <td class="xl67">186</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">8.1</td> <td class="xl67">0.28</td> <td class="xl67">0.4</td> <td class="xl67">6.9</td> <td class="xl67">0.05</td> <td class="xl67">30</td> <td class="xl67">97</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.2</td> <td class="xl67">0.32</td> <td class="xl67">0.16</td> <td class="xl67">7</td> <td class="xl67">0.045</td> <td class="xl67">30</td> <td class="xl67">136</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">7</td> <td class="xl67">0.27</td> <td class="xl67">0.36</td> <td class="xl67">20.7</td> <td class="xl67">0.045</td> <td class="xl67">45</td> <td class="xl67">170</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.3</td> <td class="xl67">0.3</td> <td class="xl67">0.34</td> <td class="xl67">1.6</td> <td class="xl67">0.049</td> <td class="xl67">14</td> <td class="xl67">132</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">8.1</td> <td class="xl67">0.22</td> <td class="xl67">0.43</td> <td class="xl67">1.5</td> <td class="xl67">0.044</td> <td class="xl67">28</td> <td class="xl67">129</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">8.1</td> <td class="xl67">0.27</td> <td class="xl67">0.41</td> <td class="xl67">1.45</td> <td class="xl67">0.033</td> <td class="xl67">11</td> <td class="xl67">63</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">8.6</td> <td class="xl67">0.23</td> <td class="xl67">0.4</td> <td class="xl67">4.2</td> <td class="xl67">0.035</td> <td class="xl67">17</td> <td class="xl67">109</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">7.9</td> <td class="xl67">0.18</td> <td class="xl67">0.37</td> <td class="xl67">1.2</td> <td class="xl67">0.04</td> <td class="xl67">16</td> <td class="xl67">75</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.6</td> <td class="xl67">0.16</td> <td class="xl67">0.4</td> <td class="xl67">1.5</td> <td class="xl67">0.044</td> <td class="xl67">48</td> <td class="xl67">143</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">8.3</td> <td class="xl67">0.42</td> <td class="xl67">0.62</td> <td class="xl67">19.25</td> <td class="xl67">0.04</td> <td class="xl67">41</td> <td class="xl67">172</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.6</td> <td class="xl67">0.17</td> <td class="xl67">0.38</td> <td class="xl67">1.5</td> <td class="xl67">0.032</td> <td class="xl67">28</td> <td class="xl67">112</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.3</td> <td class="xl67">0.48</td> <td class="xl67">0.04</td> <td class="xl67">1.1</td> <td class="xl67">0.046</td> <td class="xl67">30</td> <td class="xl67">99</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.2</td> <td class="xl67">0.66</td> <td class="xl67">0.48</td> <td class="xl67">1.2</td> <td class="xl67">0.029</td> <td class="xl67">29</td> <td class="xl67">75</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">7.4</td> <td class="xl67">0.34</td> <td class="xl67">0.42</td> <td class="xl67">1.1</td> <td class="xl67">0.033</td> <td class="xl67">17</td> <td class="xl67">171</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.5</td> <td class="xl67">0.31</td> <td class="xl67">0.14</td> <td class="xl67">7.5</td> <td class="xl67">0.044</td> <td class="xl67">34</td> <td class="xl67">133</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.2</td> <td class="xl67">0.66</td> <td class="xl67">0.48</td> <td class="xl67">1.2</td> <td class="xl67">0.029</td> <td class="xl67">29</td> <td class="xl67">75</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.4</td> <td class="xl67">0.31</td> <td class="xl67">0.38</td> <td class="xl67">2.9</td> <td class="xl67">0.038</td> <td class="xl67">19</td> <td class="xl67">102</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">6.8</td> <td class="xl67">0.26</td> <td class="xl67">0.42</td> <td class="xl67">1.7</td> <td class="xl67">0.049</td> <td class="xl67">41</td> <td class="xl67">122</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl67">7.6</td> <td class="xl67">0.67</td> <td class="xl67">0.14</td> <td class="xl67">1.5</td> <td class="xl67">0.074</td> <td class="xl67">25</td> <td class="xl67">168</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Suppose your data are in c2:i26 with row 2 containing the labels. Also suppose you want the dropdown in N3 and the result in o3:o26.

For the dropdown use data validation with the source type as list and the list as c2:i2.

Then, select o3:o26 and enter the array formula =INDEX(C3:I26,0,MATCH(N3,C2:I2,0)).

To enter an array formula complete data entry with the CTRL+SHIFT+ENTER combination rather than just the ENTER or the TAB key. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }
 
Upvote 0
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:87.33px;" /><col style="width:97.33px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; ">a</td><td style="font-family:Verdana; ">b</td><td style="font-family:Verdana; ">c</td><td style="font-family:Verdana; ">d</td><td style="font-family:Verdana; ">e</td><td style="font-family:Verdana; ">f</td><td style="font-family:Verdana; ">g</td><td style="font-family:Verdana; "> </td><td style="font-weight:bold; font-family:Verdana; ">c</td><td style="font-weight:bold; font-family:Verdana; ">Data Source</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Verdana; text-align:right; ">7</td><td style="font-family:Verdana; text-align:right; ">0.27</td><td style="font-family:Verdana; text-align:right; ">0.36</td><td style="font-family:Verdana; text-align:right; ">20.7</td><td style="font-family:Verdana; text-align:right; ">0.045</td><td style="font-family:Verdana; text-align:right; ">45</td><td style="font-family:Verdana; text-align:right; ">170</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.36</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Verdana; text-align:right; ">6.3</td><td style="font-family:Verdana; text-align:right; ">0.3</td><td style="font-family:Verdana; text-align:right; ">0.34</td><td style="font-family:Verdana; text-align:right; ">1.6</td><td style="font-family:Verdana; text-align:right; ">0.049</td><td style="font-family:Verdana; text-align:right; ">14</td><td style="font-family:Verdana; text-align:right; ">132</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.34</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Verdana; text-align:right; ">8.1</td><td style="font-family:Verdana; text-align:right; ">0.28</td><td style="font-family:Verdana; text-align:right; ">0.4</td><td style="font-family:Verdana; text-align:right; ">6.9</td><td style="font-family:Verdana; text-align:right; ">0.05</td><td style="font-family:Verdana; text-align:right; ">30</td><td style="font-family:Verdana; text-align:right; ">97</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.4</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Verdana; text-align:right; ">7.2</td><td style="font-family:Verdana; text-align:right; ">0.23</td><td style="font-family:Verdana; text-align:right; ">0.32</td><td style="font-family:Verdana; text-align:right; ">8.5</td><td style="font-family:Verdana; text-align:right; ">0.058</td><td style="font-family:Verdana; text-align:right; ">47</td><td style="font-family:Verdana; text-align:right; ">186</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.32</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Verdana; text-align:right; ">7.2</td><td style="font-family:Verdana; text-align:right; ">0.23</td><td style="font-family:Verdana; text-align:right; ">0.32</td><td style="font-family:Verdana; text-align:right; ">8.5</td><td style="font-family:Verdana; text-align:right; ">0.058</td><td style="font-family:Verdana; text-align:right; ">47</td><td style="font-family:Verdana; text-align:right; ">186</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.32</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Verdana; text-align:right; ">8.1</td><td style="font-family:Verdana; text-align:right; ">0.28</td><td style="font-family:Verdana; text-align:right; ">0.4</td><td style="font-family:Verdana; text-align:right; ">6.9</td><td style="font-family:Verdana; text-align:right; ">0.05</td><td style="font-family:Verdana; text-align:right; ">30</td><td style="font-family:Verdana; text-align:right; ">97</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.4</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Verdana; text-align:right; ">6.2</td><td style="font-family:Verdana; text-align:right; ">0.32</td><td style="font-family:Verdana; text-align:right; ">0.16</td><td style="font-family:Verdana; text-align:right; ">7</td><td style="font-family:Verdana; text-align:right; ">0.045</td><td style="font-family:Verdana; text-align:right; ">30</td><td style="font-family:Verdana; text-align:right; ">136</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.16</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Verdana; text-align:right; ">7</td><td style="font-family:Verdana; text-align:right; ">0.27</td><td style="font-family:Verdana; text-align:right; ">0.36</td><td style="font-family:Verdana; text-align:right; ">20.7</td><td style="font-family:Verdana; text-align:right; ">0.045</td><td style="font-family:Verdana; text-align:right; ">45</td><td style="font-family:Verdana; text-align:right; ">170</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.36</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Verdana; text-align:right; ">6.3</td><td style="font-family:Verdana; text-align:right; ">0.3</td><td style="font-family:Verdana; text-align:right; ">0.34</td><td style="font-family:Verdana; text-align:right; ">1.6</td><td style="font-family:Verdana; text-align:right; ">0.049</td><td style="font-family:Verdana; text-align:right; ">14</td><td style="font-family:Verdana; text-align:right; ">132</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.34</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Verdana; text-align:right; ">8.1</td><td style="font-family:Verdana; text-align:right; ">0.22</td><td style="font-family:Verdana; text-align:right; ">0.43</td><td style="font-family:Verdana; text-align:right; ">1.5</td><td style="font-family:Verdana; text-align:right; ">0.044</td><td style="font-family:Verdana; text-align:right; ">28</td><td style="font-family:Verdana; text-align:right; ">129</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.43</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Verdana; text-align:right; ">8.1</td><td style="font-family:Verdana; text-align:right; ">0.27</td><td style="font-family:Verdana; text-align:right; ">0.41</td><td style="font-family:Verdana; text-align:right; ">1.45</td><td style="font-family:Verdana; text-align:right; ">0.033</td><td style="font-family:Verdana; text-align:right; ">11</td><td style="font-family:Verdana; text-align:right; ">63</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.41</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-family:Verdana; text-align:right; ">8.6</td><td style="font-family:Verdana; text-align:right; ">0.23</td><td style="font-family:Verdana; text-align:right; ">0.4</td><td style="font-family:Verdana; text-align:right; ">4.2</td><td style="font-family:Verdana; text-align:right; ">0.035</td><td style="font-family:Verdana; text-align:right; ">17</td><td style="font-family:Verdana; text-align:right; ">109</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.4</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-family:Verdana; text-align:right; ">7.9</td><td style="font-family:Verdana; text-align:right; ">0.18</td><td style="font-family:Verdana; text-align:right; ">0.37</td><td style="font-family:Verdana; text-align:right; ">1.2</td><td style="font-family:Verdana; text-align:right; ">0.04</td><td style="font-family:Verdana; text-align:right; ">16</td><td style="font-family:Verdana; text-align:right; ">75</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.37</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-family:Verdana; text-align:right; ">6.6</td><td style="font-family:Verdana; text-align:right; ">0.16</td><td style="font-family:Verdana; text-align:right; ">0.4</td><td style="font-family:Verdana; text-align:right; ">1.5</td><td style="font-family:Verdana; text-align:right; ">0.044</td><td style="font-family:Verdana; text-align:right; ">48</td><td style="font-family:Verdana; text-align:right; ">143</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.4</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-family:Verdana; text-align:right; ">8.3</td><td style="font-family:Verdana; text-align:right; ">0.42</td><td style="font-family:Verdana; text-align:right; ">0.62</td><td style="font-family:Verdana; text-align:right; ">19.25</td><td style="font-family:Verdana; text-align:right; ">0.04</td><td style="font-family:Verdana; text-align:right; ">41</td><td style="font-family:Verdana; text-align:right; ">172</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.62</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-family:Verdana; text-align:right; ">6.6</td><td style="font-family:Verdana; text-align:right; ">0.17</td><td style="font-family:Verdana; text-align:right; ">0.38</td><td style="font-family:Verdana; text-align:right; ">1.5</td><td style="font-family:Verdana; text-align:right; ">0.032</td><td style="font-family:Verdana; text-align:right; ">28</td><td style="font-family:Verdana; text-align:right; ">112</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.38</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-family:Verdana; text-align:right; ">6.3</td><td style="font-family:Verdana; text-align:right; ">0.48</td><td style="font-family:Verdana; text-align:right; ">0.04</td><td style="font-family:Verdana; text-align:right; ">1.1</td><td style="font-family:Verdana; text-align:right; ">0.046</td><td style="font-family:Verdana; text-align:right; ">30</td><td style="font-family:Verdana; text-align:right; ">99</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.04</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-family:Verdana; text-align:right; ">6.2</td><td style="font-family:Verdana; text-align:right; ">0.66</td><td style="font-family:Verdana; text-align:right; ">0.48</td><td style="font-family:Verdana; text-align:right; ">1.2</td><td style="font-family:Verdana; text-align:right; ">0.029</td><td style="font-family:Verdana; text-align:right; ">29</td><td style="font-family:Verdana; text-align:right; ">75</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.48</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-family:Verdana; text-align:right; ">7.4</td><td style="font-family:Verdana; text-align:right; ">0.34</td><td style="font-family:Verdana; text-align:right; ">0.42</td><td style="font-family:Verdana; text-align:right; ">1.1</td><td style="font-family:Verdana; text-align:right; ">0.033</td><td style="font-family:Verdana; text-align:right; ">17</td><td style="font-family:Verdana; text-align:right; ">171</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.42</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-family:Verdana; text-align:right; ">6.5</td><td style="font-family:Verdana; text-align:right; ">0.31</td><td style="font-family:Verdana; text-align:right; ">0.14</td><td style="font-family:Verdana; text-align:right; ">7.5</td><td style="font-family:Verdana; text-align:right; ">0.044</td><td style="font-family:Verdana; text-align:right; ">34</td><td style="font-family:Verdana; text-align:right; ">133</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.14</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-family:Verdana; text-align:right; ">6.2</td><td style="font-family:Verdana; text-align:right; ">0.66</td><td style="font-family:Verdana; text-align:right; ">0.48</td><td style="font-family:Verdana; text-align:right; ">1.2</td><td style="font-family:Verdana; text-align:right; ">0.029</td><td style="font-family:Verdana; text-align:right; ">29</td><td style="font-family:Verdana; text-align:right; ">75</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.48</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-family:Verdana; text-align:right; ">6.4</td><td style="font-family:Verdana; text-align:right; ">0.31</td><td style="font-family:Verdana; text-align:right; ">0.38</td><td style="font-family:Verdana; text-align:right; ">2.9</td><td style="font-family:Verdana; text-align:right; ">0.038</td><td style="font-family:Verdana; text-align:right; ">19</td><td style="font-family:Verdana; text-align:right; ">102</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.38</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-family:Verdana; text-align:right; ">6.8</td><td style="font-family:Verdana; text-align:right; ">0.26</td><td style="font-family:Verdana; text-align:right; ">0.42</td><td style="font-family:Verdana; text-align:right; ">1.7</td><td style="font-family:Verdana; text-align:right; ">0.049</td><td style="font-family:Verdana; text-align:right; ">41</td><td style="font-family:Verdana; text-align:right; ">122</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">0.42</td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-family:Verdana; text-align:right; ">7.6</td><td style="font-family:Verdana; text-align:right; ">0.67</td><td style="font-family:Verdana; text-align:right; ">0.14</td><td style="font-family:Verdana; text-align:right; ">1.5</td><td style="font-family:Verdana; text-align:right; ">0.074</td><td style="font-family:Verdana; text-align:right; ">25</td><td style="font-family:Verdana; text-align:right; ">168</td><td > </td><td > </td><td style="font-family:Verdana; text-align:right; ">0.14</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >J2</td><td >=IF($I$1="","",INDEX<span style=' color:008000; '>($A$1:$G$25, ROW<span style=' color:#0000ff; '>($A2)</span>, MATCH<span style=' color:#0000ff; '>($I$1,$A$1:$G$1,0)</span>)</span>)</td></tr><tr></tr></table></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#0000ff;background-color:#ffFCF9; color:#000000; "><tr><td ><b>Data Validation in Spreadsheet</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Allow</td><td >Datas</td><td >Input 1</td><td >Input 2</td></tr><tr><td >I1</td><td >List</td><td > </td><td >=$A$1:$G$1</td><td > </td></tr></table></td></tr></table>
 
Upvote 0
Hi, thanks very much for your help.

Also, could you clarify what the second box is where it says data validation?

many thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
1
  • Question Question
Calculate on every third Friday of the month and show results between positive & negative weeks
2
Replies
14
Views
969
15minoffame
1
G
  • Question Question
Complex problem - how many widgets should I sell?
Replies
9
Views
641
Dan_W
D
M
  • Question Question
Count Max Open Trades by Date and Pair
Replies
1
Views
832
JamesCanale
JamesCanale
R
  • Solved
Add Dynamic calculated item to a pivot table
2 3
Replies
21
Views
2K
rbnaik
R

Forum statistics

Threads
1,224,522
Messages
6,179,292
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top