If statement -conditions meet criteria transfer to sheet 2

C

Celticshadow

Active Member
Joined
Aug 16, 2010
Messages
419
Office Version
  1. 365
Platform
  1. Windows
Hi All

May I ask the board if it is possible to transfer a row of data to a second sheet if the criteria are met in cell A3 of the first sheet as below. I trust you find this to be in order and tahnks in advance.

Kind Regards

Code: 
                                                                                                                                                                                                                                                                                                <table border="0" cellpadding="0" cellspacing="0" width="384"><colgroup><col style="width:48pt" span="6" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt;width:48pt" height="17" width="64">
</td><td class="xl68" style="width:48pt" width="64">
</td><td class="xl68" style="width:48pt" width="64">
</td><td class="xl68" style="width:48pt" width="64">
</td><td class="xl68" style="width:48pt" width="64">
</td><td class="xl68" style="width:48pt" width="64">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Condition</td><td class="xl68">Data</td><td class="xl68">Data</td><td class="xl68">Data</td><td class="xl68">Data</td><td class="xl68">Data</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">SL</td><td class="xl68">1</td><td class="xl68">5</td><td class="xl68">9</td><td class="xl68">3</td><td class="xl68">2</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">
</td><td class="xl68">7</td><td class="xl68">8</td><td class="xl68">9</td><td class="xl68">0</td><td class="xl68">0</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">SL</td><td class="xl68">1</td><td class="xl68">1</td><td class="xl68">1</td><td class="xl68">1</td><td class="xl68">1</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">
</td><td class="xl68">3</td><td class="xl68">4</td><td class="xl68">5</td><td class="xl68">0</td><td class="xl68">0</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">SL</td><td class="xl68">1</td><td class="xl68">5</td><td class="xl68">9</td><td class="xl68">2</td><td class="xl68">2</td></tr><tr style="height:12.75pt" height="17"><td colspan="6" rowspan="5" class="xl69" style="height:69.75pt;   width:288pt" height="93" width="384">IF cell A3 equals SL the I would like to pull the row of data   that meets the criteria into sheet 2 as below. The required pull through is   solely dependent on cell a3 meeting the sl requirement and not the shown   data. Data shown is for eg</td></tr><tr style="height:12.75pt" height="17"></tr><tr style="height:12.75pt" height="17"></tr><tr style="height:12.75pt" height="17"></tr><tr style="mso-height-source:userset;height:18.75pt" height="25"></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Sheet 2</td><td class="xl68">
</td><td class="xl68">
</td><td class="xl68">
</td><td class="xl68">
</td><td class="xl68">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Condition</td><td class="xl68">Data</td><td class="xl68">Data</td><td class="xl68">Data</td><td class="xl68">Data</td><td class="xl68">Data</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">SL</td><td class="xl68">1</td><td class="xl68">5</td><td class="xl68">9</td><td class="xl68">3</td><td class="xl68">2</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">SL</td><td class="xl68">1</td><td class="xl68">1</td><td class="xl68">1</td><td class="xl68">1</td><td class="xl68">1</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">SL</td><td class="xl68">1</td><td class="xl68">5</td><td class="xl68">9</td><td class="xl68">2</td><td class="xl68">2</td></tr> </tbody></table>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
fromspenny said:
Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Condition</td><td>Data</td><td>Data</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>SL</td><td style="text-align:right; ">1</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td style="text-align:right; ">7</td><td style="text-align:right; ">8</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>SL</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>
</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>SL</td><td style="text-align:right; ">1</td><td style="text-align:right; ">5</td></tr></tbody></table>
Sheet2

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:76px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Condition</td><td>Data</td><td>Data</td><td>Count of SL</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>SL</td><td style="text-align:right; ">1</td><td style="text-align:right; ">5</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>SL</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>SL</td><td style="text-align:right; ">1</td><td style="text-align:right; ">5</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>E1</td><td>=COUNTIF(Sheet1!A:A,"SL")</td></tr><tr><td>A2</td><td>{=IF(ROWS(A$2:A2)>$E$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(A$2:A2))))}</td></tr><tr><td>B2</td><td>{=IF(ROWS(B$2:B2)>$E$1,"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(B$2:B2))))}</td></tr><tr><td>C2</td><td>{=IF(ROWS(C$2:C2)>$E$1,"",INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(C$2:C2))))}</td></tr><tr><td>A3</td><td>{=IF(ROWS(A$2:A3)>$E$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(A$2:A3))))}</td></tr><tr><td>B3</td><td>{=IF(ROWS(B$2:B3)>$E$1,"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(B$2:B3))))}</td></tr><tr><td>C3</td><td>{=IF(ROWS(C$2:C3)>$E$1,"",INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(C$2:C3))))}</td></tr><tr><td>A4</td><td>{=IF(ROWS(A$2:A4)>$E$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(A$2:A4))))}</td></tr><tr><td>B4</td><td>{=IF(ROWS(B$2:B4)>$E$1,"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(B$2:B4))))}</td></tr><tr><td>C4</td><td>{=IF(ROWS(C$2:C4)>$E$1,"",INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(C$2:C4))))}</td></tr><tr><td>A5</td><td>{=IF(ROWS(A$2:A5)>$E$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(A$2:A5))))}</td></tr><tr><td>B5</td><td>{=IF(ROWS(B$2:B5)>$E$1,"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(B$2:B5))))}</td></tr><tr><td>C5</td><td>{=IF(ROWS(C$2:C5)>$E$1,"",INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(C$2:C5))))}</td></tr><tr><td>A6</td><td>{=IF(ROWS(A$2:A6)>$E$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(A$2:A6))))}</td></tr><tr><td>B6</td><td>{=IF(ROWS(B$2:B6)>$E$1,"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(B$2:B6))))}</td></tr><tr><td>C6</td><td>{=IF(ROWS(C$2:C6)>$E$1,"",INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(C$2:C6))))}</td></tr><tr><td>A7</td><td>{=IF(ROWS(A$2:A7)>$E$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(A$2:A7))))}</td></tr><tr><td>B7</td><td>{=IF(ROWS(B$2:B7)>$E$1,"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(B$2:B7))))}</td></tr><tr><td>C7</td><td>{=IF(ROWS(C$2:C7)>$E$1,"",INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(C$2:C7))))}</td></tr></tbody></table></td></tr><tr><td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>

Hi fromspenny

Many thanks for your reply but I am confused to the above. May I ask if you could show the formula in each cell so that I could copy and paste to see if it works. My apologies as I am not the sharpest knife in the box. doh

Kind Regards
Click to expand...
 
Upvote 0
Hi fromspenny

I tried sorting the above into some semblance of order and entered into my spreadsheet but I can not get it to work at all. May I ask if you or other members may be able to point me in the corect direction.

Kind Regards
 
Upvote 0
cell E1 =countif(sheet1!A:A,"SL") this gives the count of SL

Cell A2
=IF(ROWS(A$2:A2)>$E$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(A$2:A2))))

confirm with control+shift+enter

copy across and down desired number of rows
 
Upvote 0
fromspenny said:
cell E1 =countif(sheet1!A:A,"SL") this gives the count of SL

Cell A2
=IF(ROWS(A$2:A2)>$E$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A:$A="SL",ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1),ROWS(A$2:A2))))

confirm with control+shift+enter

copy across and down desired number of rows
Click to expand...

Hi fromspenny

I have attached a copy of the actual sheet as initially I thought I would be able to adjust the cell locations but I cant as I dont understand what the formula is doing (entirely my fault). Again if the data in data in a4 down equals SL then I would like to transfer that rows data to sheet two. Thanks in advance.

Kind Regards

https://docs.google.com/spreadsheet/ccc?key=0AhsVc2uffBUodHhjNHNfd0VNc1NaMkpZbThyMVRWc3c&hl=en_US
 
Upvote 0
Hi fromspenny

Many thanks for your replies. I can confirm that your solution works an absolute treat and that I am extremely grateful. May I take this oppurtunity to say that you are a credit to the board and once again the knowledge, speed and courteous manner of response is exceptional.

Thanks again.

Kind Regards
 
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
V
  • Question Question
How to Expand Components of an Assembly while preserving Dates
Replies
2
Views
76
Vog2654
V
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
111
Peter_SSs
Peter_SSs
A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
165
Peter_SSs
Peter_SSs
C
  • Solved
Split into sheets based on multiple criteria
Replies
6
Views
183
cdrobinson83
C

Forum statistics

Threads
1,224,526
Messages
6,179,322
Members
452,906
Latest member
Belthazar

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