withquestions
New Member
- Joined
- Sep 8, 2014
- Messages
- 1
I believe the title is fairly descriptive but the scenario is that I need to find a formula answer "yes" in a certain column from an origin worksheet, then copy selected cells from the same row into a next blank row and different columns of a destination worksheet within the same workbook, looping through every "new" "yes" appearing in the same column of the origin sheet. The purpose of this is to use the formula to determine when an agreement has gone through a finalization process and is ready to be moved from the Pending Documents into the Active Contracts category and worksheet. Finally, I would like the copied row to reflect "yes" again upon completion of the copying process onto the new worksheet, change the background color from default (white) to a dark gray, thus diminishing its prominence and for that text/change to exclude it from being copied into Active Contracts again. The sheet will change and will need to be updated on a daily basis, and there are sometimes 30-40 changes in a single day, which is laborious and unnecessary for such a repetitive task. I have attached an image of the before for both pages, and the desired effect.
Obviously, the find text is to operate on Origin sheet column N when text is "YES" and below row 3 (header rows 1-2), and then to copy the Destination sheet the name (column A to column A), client code (M to B) sign date (G to C), and salesperson (C to H). Then, it would change the entire row of the copied entry to gray background fill and fill in "YES" at column O indicating its completion (note that I cannot locate a tool to change the fill color in the post, so I am using red text as substitution). This appears to be the best organization of information for using the data, but if it is insurmountable, I could move columns, although I am hoping not to do so. Please note that while I have searched this forum and tried to extract solutions, I cannot find enough of an answer to be workable, as it appears that many people want to copy complete rows or want to add a new sheet for data rather than add to an existing sheet. Oh yes, this is Windows 7, Excel 2013.
Thank you enormously for sharing your mental acumen and training with a struggling learner!
Origin sheet before
<tbody>
</tbody>
<tbody>
</tbody>
Origin sheet after/desired
<tbody>
</tbody>
Destination sheet after/desired
<tbody>
</tbody>
Obviously, the find text is to operate on Origin sheet column N when text is "YES" and below row 3 (header rows 1-2), and then to copy the Destination sheet the name (column A to column A), client code (M to B) sign date (G to C), and salesperson (C to H). Then, it would change the entire row of the copied entry to gray background fill and fill in "YES" at column O indicating its completion (note that I cannot locate a tool to change the fill color in the post, so I am using red text as substitution). This appears to be the best organization of information for using the data, but if it is insurmountable, I could move columns, although I am hoping not to do so. Please note that while I have searched this forum and tried to extract solutions, I cannot find enough of an answer to be workable, as it appears that many people want to copy complete rows or want to add a new sheet for data rather than add to an existing sheet. Oh yes, this is Windows 7, Excel 2013.
Thank you enormously for sharing your mental acumen and training with a struggling learner!
Origin sheet before
| PENDING DOCUMENTS | |||||||||||||||
| Name | Date Received | Salesperson | Date of Salesperson Approval | Standard or Specifically Approved | Comments Re: Forwarding | Date Forwarded for Management Signature | Date Signed by Management | Date of Client Signature or Forwarded for Client Signature | Sign Date (Date Returned With All Signatures) | Date Forwarded to Data Entry | Original Document Returned for Filing | Client Code | Move to Active Contracts | Completed / Copied to Active Contracts | Followup Date |
| ABC Company | 8/1/2014 | JS | 8/1/2014 | Approved | Approved | 8/1/2014 | 8/1/2014 | 7/30/2014 | 8/1/2014 | 8/1/2014 | 8/1/2014 | 1234 | YES | ||
| XYZ Company | 8/1/2014 | JD | 8/1/2014 | Std | Std | 8/1/2014 | 8/1/2014 | 8/1/2014 | FALSE | ||||||
<tbody>
</tbody>
Destination sheet before
<tbody> </tbody> |
<tbody>
</tbody>
Origin sheet after/desired
| PENDING DOCUMENTS | |||||||||||||||
| Name | Date Received | Salesperson | Date of Salesperson Approval | Standard or Specifically Approved | Comments Re: Forwarding | Date Forwarded for Management Signature | Date Signed by Management | Date of Client Signature or Forwarded for Client Signature | Sign Date (Date Returned With All Signatures) | Date Forwarded to Data Entry | Original Document Returned for Filing | Client Code | Move to Active Contracts | Completed / Copied to Active Contracts | Followup Date |
| ABC Company | 8/1/2014 | JS | 8/1/2014 | Approved | Approved | 8/1/2014 | 8/1/2014 | 7/30/2014 | 8/1/2014 | 8/1/2014 | 8/1/2014 | 1234 | YES | YES | |
| XYZ Company | 8/1/2014 | JD | 8/1/2014 | Std | Std | 8/1/2014 | 8/1/2014 | 8/1/2014 | FALSE | 9/1/2014 | |||||
<tbody>
</tbody>
Destination sheet after/desired
| ACTIVE CONTRACTS | |||||||||||||||||||||||||
| MASTER CONTRACTS ABSTRACT | Client Code | Sign date | Start date | Expiration date | State | Estimated volume | Sales Person | Automatic extensions | Primary | Secondary | Tertiary | Default | Late Stage | Litigation | Loaded in System | Date of System Entry | 30 day check | 120 day review | Rate Terms | Settle-ment Terms | Special Reporting/ Remittance Requirements | Credit Bureau | Legal | Bank-ruptcy | Probate |
| AZ Corporation | az123 | 7/1/2014 | 7/1/2014 | 6/30/2015 | ST | $ 10,000.00 | JD | N/A | X | Y | 7/2/2014 | 8/6/2014 | 10/30/2014 | Standard and customary | 10% | None | Y | N | N | N | |||||
| ABC Company | 1234 | 8/1/2014 | JS | ||||||||||||||||||||||
<tbody>
</tbody>
