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 �Today�s Entries�
Where the cell tested true
To a different Worksheet �Outstanding List�
� If in worksheet �Today�s Entries� cell �D5�= YES then
Go to D6
� If in worksheet �Today�s Entries� cell �D5�= NO then
Copy entire row (�Today�s Entries� A5:AB5) then
Go To worksheet �Outstanding List A10�
� If in the worksheet Outstanding List A10=� � (blank) then
Paste (�today�s Entries� A5:AB5) in Outstanding List A10:AB10 then
� GO TO �Today�s Entries D6 else
� If Outstanding List A11=� � (blank) then
Paste (�today�s Entries� A5:AB5) in Outstanding List A11:AB11 then
� Go To Today�s Worksheet D6 else
If in worksheet �Today�s 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

Hi Debi

Could you not just use Autofilter.
Mark your range of headings, then Data>Filter>Autofilter 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 “Today’s Entries”
>Where the cell tested true
>To a different Worksheet “Outstanding List”
>· If in worksheet “Today’s Entries” cell “D5”= YES then
>Go to D6
>· If in worksheet “Today’s Entries” cell “D5”= NO then
>Copy entire row (“Today’s Entries” A5:AB5) then
>Go To worksheet “Outstanding List A10”
>· If in the worksheet Outstanding List A10=” “ (blank) then
>Paste (“today’s Entries” A5:AB5) in Outstanding List A10:AB10 then
>· GO TO “Today’s Entries D6 else
>· If Outstanding List A11=” “ (blank) then
>Paste (“today’s Entries” A5:AB5) in Outstanding List A11:AB11 then
>· Go To Today’s Worksheet D6 else
>If in worksheet “Today’s 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
>
>
>

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 Data>Filter>Autofilter 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 “Today’s Entries”
> >Where the cell tested true
> >To a different Worksheet “Outstanding List”
> >· If in worksheet “Today’s Entries” cell “D5”= YES then
> >Go to D6
> >· If in worksheet “Today’s Entries” cell “D5”= NO then
> >Copy entire row (“Today’s Entries” A5:AB5) then
> >Go To worksheet “Outstanding List A10”
> >· If in the worksheet Outstanding List A10=” “ (blank) then
> >Paste (“today’s Entries” A5:AB5) in Outstanding List A10:AB10 then
> >· GO TO “Today’s Entries D6 else
> >· If Outstanding List A11=” “ (blank) then
> >Paste (“today’s Entries” A5:AB5) in Outstanding List A11:AB11 then
> >· Go To Today’s Worksheet D6 else
> >If in worksheet “Today’s 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
> >
> >
> >
>

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 Data>Filter>Autofilter 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 “Today’s Entries”
>>>Where the cell tested true
>>>To a different Worksheet “Outstanding List”
>>>· If in worksheet “Today’s Entries” cell “D5”= YES then
>>>Go to D6
>>>· If in worksheet “Today’s Entries” cell “D5”= NO then
>>>Copy entire row (“Today’s Entries” A5:AB5) then
>>>Go To worksheet “Outstanding List A10”
>>>· If in the worksheet Outstanding List A10=” “ (blank) then
>>>Paste (“today’s Entries” A5:AB5) in Outstanding List A10:AB10 then
>>>· GO TO “Today’s Entries D6 else
>>>· If Outstanding List A11=” “ (blank) then
>>>Paste (“today’s Entries” A5:AB5) in Outstanding List A11:AB11 then
>>>· Go To Today’s Worksheet D6 else
>>>If in worksheet “Today’s 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
>>>
>>>
>>>
>>>
>>>

Hi Debi

Another alternative of course, would be to stick with Autofilter.
Switch on the macro recorder Tools>Macro>Record 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 Data>Filter>Autofilter 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 “Today’s Entries”
>>>Where the cell tested true
>>>To a different Worksheet “Outstanding List”
>>>· If in worksheet “Today’s Entries” cell “D5”= YES then
>>>Go to D6
>>>· If in worksheet “Today’s Entries” cell “D5”= NO then
>>>Copy entire row (“Today’s Entries” A5:AB5) then
>>>Go To worksheet “Outstanding List A10”
>>>· If in the worksheet Outstanding List A10=” “ (blank) then
>>>Paste (“today’s Entries” A5:AB5) in Outstanding List A10:AB10 then
>>>· GO TO “Today’s Entries D6 else
>>>· If Outstanding List A11=” “ (blank) then
>>>Paste (“today’s Entries” A5:AB5) in Outstanding List A11:AB11 then
>>>· Go To Today’s Worksheet D6 else
>>>If in worksheet “Today’s 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
>>>
>>>
>>>
>>>
>>>