search and retrieve [SOLVED]

jethro · Mar 24th 2003

How can I do the following:
search trough a certain reach off cells for a word.
As soon as I find the word, I want to use a few cells from the where the word is in.

Can someone help me?

Will Riley · Mar 24th 2003

Sorry, bit late here in the UK....

Could you have another go at explaining that one buddy :wink1:

Quote

search trough a certain reach off cells for a word. As soon as I find the word, I want to use a few cells from the where the word is in.

Anonymous · Mar 24th 2003

sounds like a job for =VLOOKUP

if "reach off" = "range of"

you want to search down a column of cells and when it finds "CHRIS" you want it to bring back the cells next to "CHRIS" ?

something like that ?

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%><TR><TD COLSPAN=9 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - Book5</TD><TD ALIGN=RIGHT>___Running: xl97 : OS = Windows (32-bit) 4.90</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=9><TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%><TR><TD>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD><TD ALIGN=RIGHT VALIGN=MIDDLE><FORM NAME='formCb7552370787'><INPUT TYPE='Button' NAME='btCb5705426622' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb0487398093.sltNb8859926925.value);'></FORM></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=9><TABLE BORDER=0><TR><Form name='formFb0487398093'><TD WIDTH=5% ALIGN=CENTER BGCOLOR=White><SELECT NAME='sltNb8859926925' onChange='document.formFb0487398093.txbFb1960511720.value = document.formFb0487398093.sltNb8859926925.value'><option value='=VLOOKUP(A3,E2:F10,2,0)'>B3</select></TD><TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD ALIGN=LEFT BGCOLOR=White><input type='text' name='txbFb1960511720' size='120' value='=VLOOKUP(A3,E2:F10,2,0)'></TD></form></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>F</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>G</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>H</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >search for :</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Name</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >John</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >10</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >CHRIS</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(A3,E2:F10,2,0)')>50</A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Paul</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >20</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >George</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >30</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Ringo</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >40</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM >CHRIS</TD><TD BGCOLOR=#FF99CC ALIGN=Right VALIGN=BOTTOM >50</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Harry</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>8</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Sally</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >70</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>9</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Mary</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >80</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>10</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Mungo</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >90</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>11</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>12</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>13</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=9>Sheet1</TD></TR></TABLE> To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs. The above image was automatically generated by [HtmlMaker 2.0@BETA]If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm>click here</A> to download This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]>Ivan F Moala</A> All credit to <A HREF=mailto:[email protected]>Colo</A> </CENTER>

jethro · Mar 24th 2003

Excuse me for my bad english....

What I'm trying to do:
o search trough and excelsheet for a certain value
o when I find the value I want to use the entire row where the value is in

Example:
If I find my value in row 2 colum B, than I want to use all the data in the entire row 2.
Do you understand?

jethro · Mar 24th 2003

Quote

you want to search down a column of cells and when it finds "CHRIS" you want it to bring back the cells next to "CHRIS" ?

something like that ?

Yes, that's want I want to use all the cells in the row where "Chris" is found.
I can't seem to figure out how to do this with the verticallookup.
Can you help me?

Anonymous · Mar 24th 2003

hmmmm....... this can be done with VLOOKUP, although I'll wait for a better solution :

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%><TR><TD COLSPAN=15 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - Book5</TD><TD ALIGN=RIGHT>___Running: xl97 : OS = Windows (32-bit) 4.90</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=15><TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%><TR><TD>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD><TD ALIGN=RIGHT VALIGN=MIDDLE><FORM NAME='formCb2339942116'><INPUT TYPE='Button' NAME='btCb9273296640' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb4473621504.sltNb5698059465.value);'></FORM></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=15><TABLE BORDER=0><TR><Form name='formFb4473621504'><TD WIDTH=5% ALIGN=CENTER BGCOLOR=White><SELECT NAME='sltNb5698059465' onChange='document.formFb4473621504.txbFb5434202903.value = document.formFb4473621504.sltNb5698059465.value'><option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(B1),0)'>B18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(C1),0)'>C18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(D1),0)'>D18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(E1),0)'>E18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(F1),0)'>F18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(G1),0)'>G18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(H1),0)'>H18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(I1),0)'>I18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(J1),0)'>J18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(K1),0)'>K18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(L1),0)'>L18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(M1),0)'>M18<option value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(N1),0)'>N18</select></TD><TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD ALIGN=LEFT BGCOLOR=White><input type='text' name='txbFb5434202903' size='120' value='=VLOOKUP($A18,$A$1:$N$10,COLUMN(B1),0)'></TD></form></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>F</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>G</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>H</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>I</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>J</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>K</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>L</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>M</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>N</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >John</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >10</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >11</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >12</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >13</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >14</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Paul</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >10</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >12</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >14</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >16</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >18</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >20</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >22</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >24</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >26</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >28</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >George</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >12</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >15</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >18</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >21</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >24</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >27</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >30</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >33</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >36</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >39</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >42</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Ringo</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >12</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >16</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >20</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >24</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >28</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >32</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >36</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >40</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >44</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >48</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >52</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >56</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >CHRIS</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >10</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >15</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >20</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >25</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >30</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >35</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >40</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >45</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >50</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >55</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >65</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >70</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Harry</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >12</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >18</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >24</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >30</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >36</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >42</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >48</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >54</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >66</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >72</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >78</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >84</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Sally</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >14</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >21</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >28</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >35</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >42</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >49</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >56</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >63</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >70</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >77</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >84</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >91</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >98</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>8</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Mary</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >16</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >24</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >32</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >40</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >48</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >56</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >64</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >72</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >80</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >88</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >96</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >104</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >112</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>9</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Mungo</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >18</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >27</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >36</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >45</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >54</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >63</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >72</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >81</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >90</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >99</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >108</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >117</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >126</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>10</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Midge</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >20</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >30</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >40</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >50</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >70</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >80</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >90</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >110</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >120</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >130</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >140</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>11</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>12</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>13</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>14</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>15</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>16</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Search for :</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>17</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>18</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >CHRIS</TD><TD BGCOLOR=#FF99CC ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(B1),0)')>10</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(C1),0)')>15</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(D1),0)')>20</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(E1),0)')>25</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(F1),0)')>30</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(G1),0)')>35</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(H1),0)')>40</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(I1),0)')>45</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(J1),0)')>50</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(K1),0)')>55</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(L1),0)')>60</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(M1),0)')>65</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP($A18,$A$1:$N$10,COLUMN(N1),0)')>70</A></TD></TR><TR><TD COLSPAN=15>Sheet1</TD></TR></TABLE> To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs. The above image was automatically generated by [HtmlMaker 2.0@BETA]If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm>click here</A> to download This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]>Ivan F Moala</A> All credit to <A HREF=mailto:[email protected]>Colo</A> </CENTER>

=column(B1) will bring back the column number of B1 - ie "2" as B is the 2nd column

so we use this in the 3rd arguement of VLOOKUP as it will increase as you copy it over

see the pink formula, it can be copied over

this may not be the mnost efficient method !

Will Riley · Mar 24th 2003

That'll be alot of VLOOKUPS.... anyway, in response to Chris, here's a VB solution.

The example copies entire rows of data from sheet 1 to next available row in sheet 2 where values in column 1 match the value in cell A1.

You can adjust the code to suit - but feel free to post back if you struggle...

HTH

danielrmz · Mar 24th 2003

I want to know if there ir any way, to do the following:

-I have a list in sheet1, with names, values, discounts, etc.

I want to know if there is any function that i can use. I want to have a cell in sheet2, that if i put a number, for example 2, bring me back 2 or 3 different rows from sheet 1.

for example if the number of the cell is 3, I want to bring to the same sheet (sheet2), the complete row number 165 and the complete row number 174
but if the number of the cell is 7, i need to bring different rows for example 12 and 5, but i will never end using ifs, also it only can be 7 nested.

what can i use?

thanks

*** Daniel - looks like you hit the wrong button : I'll post this up as it's own thread for you so you might get better attention*** Chris Davison

jethro · Mar 25th 2003

Can you explain the script you gave to me?
I'm not quite sure how everything works.

jethro · Mar 25th 2003

I have 2 questions:
1)
Set cl = Sheets(Sheet2).[A65536].End(xlUp)(2) ' set paste rng

I want the pasting to begin on the second row (for exampe), how does that work?

2)
Is the following possible:
A script which copy's all the rows with text starting at row 4, but if the sheetname starts with an underscore (_), than it has to skipt that sheet.

I'm not so familair with this VB, I know PHP quite well, but I guess I can't use that in combination with excel?

Will Riley · Mar 26th 2003

jethro,

I'll take a look at this tonight as I can't really upload/download at work....

I'll try to explain the code (it may differ a little form the test file, but the principle is the same)

Code

Sub conditcopy()


Dim cl As Range




Application.ScreenUpdating = False
'turn off screenupdating


Sheets("Sheet1").Select
    
    For x = 4 To 30000 ' sheet1 - for rows 4 to 30000
    
Set cl = Sheets("Sheet2").[A65536].End(xlUp)(2)
' sets paste rng to sheet2 row 2 downwards




'logical test copy & paste code here for ColA values
'that = cell "A1" value
    If Cells(x, 1).Value = Range("A1").Value Then
           Cells(x, 1).EntireRow.Copy _
                cl
    End If
    Next x ' goto next row


'explanation: each row has been given a value x (hence "Next x")
'therefore in row 4 Cell(x,1) will be cell A4
'in row 5 Cell(x,1) will be A5 and so on
'so, this is just looping down each row, testing
'the criteria "Is A5 = A1 & If so copy - and so on
' The number of rows to loop down is defined
' earlier in the code "For x = 4 To 30000"
    
Application.ScreenUpdating = True
'turn screen updating back on


End Sub

Display More

Hope this helps

jethro · Mar 26th 2003

I finally figured out want I want exactly:

In the range of B10:B50 I'll put a differen word on every row.
When I click a button the script takes als the words in the range and for every word it goes to the sheet with the same name. From that sheet it copy's al the rows with text starting at row 10.
The script pastes that text in a sheet called word_data_here starting at row 10.
But before it starts pasting the first text the script must clear everything that is in the sheet word_data_here starting at row 10.

Is this possible?

I realize that the thing I'm asking is a bit complicated, the problem is that I don't know very mucht about VB and I need this script for my excelsheet to work.

I hope that someone can help me out with this.

Will Riley · Mar 27th 2003

Hmmmm ... the dreaded scope-creep strikes again! :wink1:

Don't s'pose there's a chance of seeing an EG sheet that I could have a go at for you? Otherwise we could be here a LONG old time...

jethro · Mar 27th 2003

Quote

Don't s'pose there's a chance of seeing an EG sheet that I could have a go at for you? Otherwise we could be here a LONG old time...

EG sheet?

Will Riley · Mar 27th 2003

EG Example .... sorry :wink1:

jethro · Mar 27th 2003

I think that's a good idea, if you give me your e-mail than I'll send you the file.
I think that's the easiest way.

Will Riley · Mar 28th 2003

Jethro

I'd suggest posting a "sanitised" (i.e. no confidential info) file up here to the board so it opens it up to all skills... not being awkward but if you open it up, you'll probably get a quicker solution rather than just relying on my (sometimes) very scarce free time.

Hope you understand. :beergrin:

jethro · Mar 28th 2003

Off course, no problem, i've added the sheet as an attachment.
I've tried to explain the thing that i wanted in the "overall" sheet.
I really hope that someone can help me out.
Any questions are welcome.

yjoshi · Mar 29th 2003

This code will give you what you say you want

Private Sub cmd_copy_data_Click()
'By Yogendra Joshi
Dim sht As Worksheet
'Clear Existing Data
Sheets("_Overall").Range("A7").Select
If (ActiveCell.SpecialCells(xlLastCell).Row) > 6 Then
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Clear
End If

'Checking for Worksheets and copying data
For Each sht In Worksheets
If Left(sht.Name, 1) <> "_" Then
With sht
.Activate
.Range("A7").Activate
If (ActiveCell.SpecialCells(xlLastCell).Row) > 6 Then
.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy _
Destination:=Sheets("_Overall").Range("A65536").End(xlUp).Offset(1, 0)
End If
End With
End If
Next
Sheets("_Overall").Activate
End Sub

I have tested with lot of crab data, and also encoded the checking for sheets where there is no data, so that the wrong data does not come in!!!

Also attached the sheet.

Hope this helps.

Also please avoid bringing in toooo many issues in one quote. Open different postes for different issues. This will avoid the quotes getting too bulky and confusing.

What say???

jethro · Mar 29th 2003

Quote

Originally posted by yjoshi
This code will give you what you say you want

I have tested with lot of crab data, and also encoded the checking for sheets where there is no data, so that the wrong data does not come in!!!

Thanks, this really works great!!

Quote

Also please avoid bringing in toooo many issues in one quote. Open different postes for different issues. This will avoid the quotes getting too bulky and confusing.

You're right, thanks for the tip and your help!!

search and retrieve [SOLVED]

Participate now!

Share