Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Debi
 
Posts: n/a
Default 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   Report Post  
Roger Govier
 
Posts: n/a
Default

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   Report Post  
Debi
 
Posts: n/a
Default


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   Report Post  
Roger Govier
 
Posts: n/a
Default

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   Report Post  
Roger Govier
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro help - copy a cell down gjcase Excel Discussion (Misc queries) 3 September 4th 05 05:09 AM
How can I copy values from one cell in a column down to the next c GSingh Excel Discussion (Misc queries) 9 August 16th 05 11:18 AM
How do I copy every 8th cell in a column siverfox19482 Excel Discussion (Misc queries) 2 March 16th 05 06:47 PM
Copy Word table into Excel cell by cell hg Excel Discussion (Misc queries) 3 December 15th 04 05:43 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 06:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"