Home |
Search |
Today's Posts |
#1
|
|||
|
|||
hpw do I logic test a cell then copy the row to diff. SS
Would someone have an example of how I would use vlookup to produce the
outcome from the if statements that are listed below. I want to be able to check one cell in ever row, row by row intil there are no more entries: Copy the data in the row from the first worksheet €śTodays Entries€ť Where the cell tested true To a different Worksheet €śOutstanding List€ť · If in worksheet €śTodays Entries€ť cell €śD5€ť= YES then Go to D6 · If in worksheet €śTodays Entries€ť cell €śD5€ť= NO then Copy entire row (€śTodays Entries€ť A5:AB5) then Go To worksheet €śOutstanding List A10€ť · If in the worksheet Outstanding List A10=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A10:AB10 then · GO TO €śTodays Entries D6 else · If Outstanding List A11=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A11:AB11 then · Go To Todays Worksheet D6 else If in worksheet €śTodays Entries€ť cell €śD5€ť = €ś €ś (blank) then · Stop. The desire is to check an entire worksheet for a No in Column D line by line if true to copy the entire row associated with the Cell (i.e. D1=NO) to a completely different separate worksheet if not true to check the next cell and so on, When the test in not true go to the next cell to check if it is true and follow same paths until you the next cell being tested is blank then stop the checking. When you paste the row from the first worksheet into the second on the next time you have a row to past the second worksheet will need to go down the first empty row Step one cell down from the previous Cell Tested |
#2
|
|||
|
|||
Hi Debi
Could you not just use Autofilter. Mark your range of headings, then DataFilterAutofilter select the drop down on column D and select NO You will then see a filtered list of just those lines. Copy the block of data and paste to your other sheet. Regards Roger Govier Debi wrote: Would someone have an example of how I would use vlookup to produce the outcome from the if statements that are listed below. I want to be able to check one cell in ever row, row by row intil there are no more entries: Copy the data in the row from the first worksheet €śTodays Entries€ť Where the cell tested true To a different Worksheet €śOutstanding List€ť · If in worksheet €śTodays Entries€ť cell €śD5€ť= YES then Go to D6 · If in worksheet €śTodays Entries€ť cell €śD5€ť= NO then Copy entire row (€śTodays Entries€ť A5:AB5) then Go To worksheet €śOutstanding List A10€ť · If in the worksheet Outstanding List A10=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A10:AB10 then · GO TO €śTodays Entries D6 else · If Outstanding List A11=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A11:AB11 then · Go To Todays Worksheet D6 else If in worksheet €śTodays Entries€ť cell €śD5€ť = €ś €ś (blank) then · Stop. The desire is to check an entire worksheet for a No in Column D line by line if true to copy the entire row associated with the Cell (i.e. D1=NO) to a completely different separate worksheet if not true to check the next cell and so on, When the test in not true go to the next cell to check if it is true and follow same paths until you the next cell being tested is blank then stop the checking. When you paste the row from the first worksheet into the second on the next time you have a row to past the second worksheet will need to go down the first empty row Step one cell down from the previous Cell Tested |
#3
|
|||
|
|||
Roger, Thanks for your suggestion it sounds much less difficult however is there a way to automatically do as you suggest so that someone else could simple open a worksheet and have the task of already been performed so that all they had to do is print the new list that is comprised of all of the "no's" from the other worksheet? "Roger Govier" wrote: Hi Debi Could you not just use Autofilter. Mark your range of headings, then DataFilterAutofilter select the drop down on column D and select NO You will then see a filtered list of just those lines. Copy the block of data and paste to your other sheet. Regards Roger Govier Debi wrote: Would someone have an example of how I would use vlookup to produce the outcome from the if statements that are listed below. I want to be able to check one cell in ever row, row by row intil there are no more entries: Copy the data in the row from the first worksheet €śTodays Entries€ť Where the cell tested true To a different Worksheet €śOutstanding List€ť · If in worksheet €śTodays Entries€ť cell €śD5€ť= YES then Go to D6 · If in worksheet €śTodays Entries€ť cell €śD5€ť= NO then Copy entire row (€śTodays Entries€ť A5:AB5) then Go To worksheet €śOutstanding List A10€ť · If in the worksheet Outstanding List A10=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A10:AB10 then · GO TO €śTodays Entries D6 else · If Outstanding List A11=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A11:AB11 then · Go To Todays Worksheet D6 else If in worksheet €śTodays Entries€ť cell €śD5€ť = €ś €ś (blank) then · Stop. The desire is to check an entire worksheet for a No in Column D line by line if true to copy the entire row associated with the Cell (i.e. D1=NO) to a completely different separate worksheet if not true to check the next cell and so on, When the test in not true go to the next cell to check if it is true and follow same paths until you the next cell being tested is blank then stop the checking. When you paste the row from the first worksheet into the second on the next time you have a row to past the second worksheet will need to go down the first empty row Step one cell down from the previous Cell Tested |
#4
|
|||
|
|||
Hi Debi
You could automate it with Advanced Filter instead of Autofilter. Take a look at Debra Dalgleish's site for information on how to achieve this. http://www.contextures.com/xladvfilter01.html#ExtractWs Regards Roger Govier Debi wrote: Roger, Thanks for your suggestion it sounds much less difficult however is there a way to automatically do as you suggest so that someone else could simple open a worksheet and have the task of already been performed so that all they had to do is print the new list that is comprised of all of the "no's" from the other worksheet? "Roger Govier" wrote: Hi Debi Could you not just use Autofilter. Mark your range of headings, then DataFilterAutofilter select the drop down on column D and select NO You will then see a filtered list of just those lines. Copy the block of data and paste to your other sheet. Regards Roger Govier Debi wrote: Would someone have an example of how I would use vlookup to produce the outcome from the if statements that are listed below. I want to be able to check one cell in ever row, row by row intil there are no more entries: Copy the data in the row from the first worksheet €śTodays Entries€ť Where the cell tested true To a different Worksheet €śOutstanding List€ť · If in worksheet €śTodays Entries€ť cell €śD5€ť= YES then Go to D6 · If in worksheet €śTodays Entries€ť cell €śD5€ť= NO then Copy entire row (€śTodays Entries€ť A5:AB5) then Go To worksheet €śOutstanding List A10€ť · If in the worksheet Outstanding List A10=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A10:AB10 then · GO TO €śTodays Entries D6 else · If Outstanding List A11=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A11:AB11 then · Go To Todays Worksheet D6 else If in worksheet €śTodays Entries€ť cell €śD5€ť = €ś €ś (blank) then · Stop. The desire is to check an entire worksheet for a No in Column D line by line if true to copy the entire row associated with the Cell (i.e. D1=NO) to a completely different separate worksheet if not true to check the next cell and so on, When the test in not true go to the next cell to check if it is true and follow same paths until you the next cell being tested is blank then stop the checking. When you paste the row from the first worksheet into the second on the next time you have a row to past the second worksheet will need to go down the first empty row Step one cell down from the previous Cell Tested |
#5
|
|||
|
|||
Hi Debi
Another alternative of course, would be to stick with Autofilter. Switch on the macro recorder ToolsMacroRecord as you carry out the task first time, then save the macro. You could attach the macro to a button on the sheet or the toolbar and all any other user would have to do is press the button. Regards Roger Govier Debi wrote: Roger, Thanks for your suggestion it sounds much less difficult however is there a way to automatically do as you suggest so that someone else could simple open a worksheet and have the task of already been performed so that all they had to do is print the new list that is comprised of all of the "no's" from the other worksheet? "Roger Govier" wrote: Hi Debi Could you not just use Autofilter. Mark your range of headings, then DataFilterAutofilter select the drop down on column D and select NO You will then see a filtered list of just those lines. Copy the block of data and paste to your other sheet. Regards Roger Govier Debi wrote: Would someone have an example of how I would use vlookup to produce the outcome from the if statements that are listed below. I want to be able to check one cell in ever row, row by row intil there are no more entries: Copy the data in the row from the first worksheet €śTodays Entries€ť Where the cell tested true To a different Worksheet €śOutstanding List€ť · If in worksheet €śTodays Entries€ť cell €śD5€ť= YES then Go to D6 · If in worksheet €śTodays Entries€ť cell €śD5€ť= NO then Copy entire row (€śTodays Entries€ť A5:AB5) then Go To worksheet €śOutstanding List A10€ť · If in the worksheet Outstanding List A10=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A10:AB10 then · GO TO €śTodays Entries D6 else · If Outstanding List A11=€ť €ś (blank) then Paste (€śtodays Entries€ť A5:AB5) in Outstanding List A11:AB11 then · Go To Todays Worksheet D6 else If in worksheet €śTodays Entries€ť cell €śD5€ť = €ś €ś (blank) then · Stop. The desire is to check an entire worksheet for a No in Column D line by line if true to copy the entire row associated with the Cell (i.e. D1=NO) to a completely different separate worksheet if not true to check the next cell and so on, When the test in not true go to the next cell to check if it is true and follow same paths until you the next cell being tested is blank then stop the checking. When you paste the row from the first worksheet into the second on the next time you have a row to past the second worksheet will need to go down the first empty row Step one cell down from the previous Cell Tested |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro help - copy a cell down | Excel Discussion (Misc queries) | |||
How can I copy values from one cell in a column down to the next c | Excel Discussion (Misc queries) | |||
How do I copy every 8th cell in a column | Excel Discussion (Misc queries) | |||
Copy Word table into Excel cell by cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |