Is this possible?

[MatthewM]

I have Excel 2003 and I want to take data from one page of a worksheet to another using a certain parameter. It would be something like this

Column A________ Column B ______Column C
Equipment __________P.M.________ Hours

Now I want the search parameter to be anything over 250 hours in Column C. So if Column C is over 250 hours I want the whole row to be moved to this other page.

Also, is there anyway to re-arrange the data once it gets over to the other page. There are more columns on the source page than I want on the new one. And I want to change the order of the data coming over. If not that's ok, even getting it over to another page would save me hours of tedium.

I know I could just get some other software and it would make it way easier, but I just work here and dont make those kinds of decisions, although I have suggested that. Any help would be appreciated.

Matt

 

[West Man]

How about using auto filter-custom greaert than 250 on the column. Copy and paste into the new sheet. delete the unneeded columns and sort as needed.

 

[Yahya]

in sheet1
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Equipment </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">P.M.</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Hours</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">211</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">422</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">751</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">111</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">231</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">189</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">821</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">933</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">147</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">770</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">138</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">305</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">109</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">188</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">117</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">126</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">149</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">791</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

in sheet 2
in A1 =COUNTIF(Sheet1!$C$2:$C$21,">250")
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Equipment </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">P.M.</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Hours</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">422</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">751</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">821</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">933</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">770</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">305</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">791</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A4</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$3:A3</font>)<=$A$1,INDEX(<font color="Red">Sheet1!A$2:A$21,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$C$2:$C$21>250,ROW(<font color="Teal">Sheet1!$A$2:$A$21</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$3:A3</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$3:B3</font>)<=$A$1,INDEX(<font color="Red">Sheet1!B$2:B$21,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$C$2:$C$21>250,ROW(<font color="Teal">Sheet1!$A$2:$A$21</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$3:B3</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$3:C3</font>)<=$A$1,INDEX(<font color="Red">Sheet1!C$2:C$21,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$C$2:$C$21>250,ROW(<font color="Teal">Sheet1!$A$2:$A$21</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$3:C3</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[MatthewM]

Thanks both of you.

West Man your solution would work, but I want to automate it, so that the final list is generated once the hours calculated on the first sheet are greater than 250.

Yahya, the countif gives me a numerical value of how many are over 250. And I don't understand arrays(looking into it now though)

I am much further along than I was, so thanks again. Anyone else with an idea don't be shy.

Matthew

 

[Yahya]

i used COUNTIF to ignore Error
this is my page
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Equipment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">P.M.</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Hours</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;">422</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=COUNTIF(<font color="Blue">Sheet1!$C$2:$C$21,">250"</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A4</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$3:A3</font>)<=$A$1,INDEX(<font color="Red">Sheet1!A$2:A$21,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$C$2:$C$21>250,ROW(<font color="Teal">Sheet1!$A$2:$A$21</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$3:A3</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$3:B3</font>)<=$A$1,INDEX(<font color="Red">Sheet1!B$2:B$21,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$C$2:$C$21>250,ROW(<font color="Teal">Sheet1!$A$2:$A$21</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$3:B3</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$3:C3</font>)<=$A$1,INDEX(<font color="Red">Sheet1!C$2:C$21,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$C$2:$C$21>250,ROW(<font color="Teal">Sheet1!$A$2:$A$21</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$3:C3</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[MatthewM]

Thanks Yahya, I think I know what you are saying now. I think.....

So if my actual worksheet is 106 rows long and starts on row 6 the array would need to look like this?

{=IF(ROWS($A$6:A6)<=$A$1,INDEX(Sheet1!A$6:A$106,SMALL(IF(Sheet1!$C$6:$C$106>250,ROW(Sheet1!$A$6:$A$106)-ROW(Sheet1!$A$6)+1),ROWS($A$6:A6))),"")}

 

[Yahya]

Thanks Yahya, I think I know what you are saying now. I think.....

So if my actual worksheet is 106 rows long and starts on row 6 the array would need to look like this?

{=IF(ROWS($A$6:A6)<=$A$1,INDEX(Sheet1!A$6:A$106,SMALL(IF(Sheet1!$C$6:$C$106>250,ROW(Sheet1!$A$6:$A$106)-ROW(Sheet1!$A$6)+1),ROWS($A$6:A6))),"")}

Yes Matthew

 

[MatthewM]

Ok, on the destination worksheet I now have the first row of the source sheet(thanks), but that's all I have and it's not going through the > 250 parameter. I tried changing all the A's in the B and C arrays to B and C respectively, but that didn't help. Thoughts?

Matthew

 

[Yahya]

could you post small sample from your data,
and please to try to use MrExcel HTML Maker or Excel jeanie

 

[MatthewM]

Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">26</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1118</TD><TD style="BACKGROUND-COLOR: #ffff00">84" Sakai Padfoot Roller (2004)</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">9/28/10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">3078</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">3312</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">8/29/11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">234</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">2498</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">27</TD><TD style="TEXT-ALIGN: center">1119</TD><TD>Hyundai HL740-7E Wheel Loader (2004)</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">7/5/11</TD><TD style="TEXT-ALIGN: center">4458</TD><TD style="TEXT-ALIGN: center">4561</TD><TD style="TEXT-ALIGN: center">8/29/11</TD><TD style="TEXT-ALIGN: center">103</TD><TD style="TEXT-ALIGN: center">2498</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">28</TD><TD style="TEXT-ALIGN: center">1121</TD><TD>25KW Ingersall Rand Generator (2005)</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">11/10/10</TD><TD style="TEXT-ALIGN: center">8411</TD><TD style="TEXT-ALIGN: center">9296</TD><TD style="TEXT-ALIGN: center">1/10/11</TD><TD style="TEXT-ALIGN: center">885</TD><TD style="TEXT-ALIGN: center">SHOP</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">29</TD><TD style="TEXT-ALIGN: center">1122</TD><TD>2005 Ford F650 Mechanics Truck</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">4/6/10</TD><TD style="TEXT-ALIGN: center">121879</TD><TD style="TEXT-ALIGN: center">121879</TD><TD style="TEXT-ALIGN: center">4/6/10</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">CARL</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">30</TD><TD style="TEXT-ALIGN: center">1123</TD><TD>6" Gorman-Rupp Water Pump</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">3/2/11</TD><TD style="TEXT-ALIGN: center">804</TD><TD style="TEXT-ALIGN: center">804</TD><TD style="TEXT-ALIGN: center">3/2/11</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">2619</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">31</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1125</TD><TD style="BACKGROUND-COLOR: #ffff00">20KW Isuzu Generator</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">7/28/11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">744</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1299</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">8/29/11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">555</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">2498</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">32</TD><TD style="TEXT-ALIGN: center">1129</TD><TD>1998 GMC Water Truck</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">10/20/08</TD><TD style="TEXT-ALIGN: center">123005</TD><TD style="TEXT-ALIGN: center">125388</TD><TD style="TEXT-ALIGN: center">8/29/11</TD><TD style="TEXT-ALIGN: center">2383</TD><TD style="TEXT-ALIGN: center">2498</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">33</TD><TD style="TEXT-ALIGN: center">1130</TD><TD>6" Gorman-Rupp Pump</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">8/4/10</TD><TD style="TEXT-ALIGN: center">4920</TD><TD style="TEXT-ALIGN: center">5135</TD><TD style="TEXT-ALIGN: center">7/28/11</TD><TD style="TEXT-ALIGN: center">215</TD><TD style="TEXT-ALIGN: center">2560</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">34</TD><TD style="TEXT-ALIGN: center">1134</TD><TD>Mitsubishi FG25N-LP 5000# Forklift</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">124</TD><TD style="TEXT-ALIGN: center">5/21/07</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">SHOP</TD></TR></TBODY></TABLE>

OWNEDEQUIPMENT​

Ok so here is the actual database