• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy a row to new ws when conditional format rule is met on source ws

S

Sasown

New Member
My source worksheet, "Employees", contains all employees for two different companies (Valley Tool and Valley Farm).
I want the worksheet to do two different things; whenever any value is entered into a cell in either column C or E, the text color changes to red for that row, and then copies that row to one of the two other worksheets. If value is entered into column C "Valley Tool Inactive" should copy to "Past VT". If value is entered into column E "Valley Farm Inactive" should copy to "Past VFSS". As employees become inactive, the lists on the two "Past" worksheets will grow.
Attached is an example of what the spreadsheet looks like. I thought I had the first part of my request figured out but it only worked for the first row of data and for some reason, not columns G and H. I cleared all conditional formatting before attaching the file.
 

Attachments

  • Employee List.xlsx
    10.6 KB · Views: 5
Hi, Sasown!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question, a few clarifications, please:
a) once you paste the row cells into 2nd or 3rd worksheet, what should happen to data in 1st sheet? can it be edited twice or more times?
b) once the values are pasted, if you modify 1st sheet related row, what should happen to 2nd and 3rd sheets? data in 2nd/3rd should be updated or added as new entries?
c) once the values are pasted, if you modify 2nd/3rd sheets, what should happen to 1st sheet? data in 1st sheet should be updated or...?

Regards!
 
I'm sorry, I was looking at the forums and made the decision to write a question and totally didn't look over the new member information. :( I have now and even written a thing about me! Thank you for your time to help me with this. By the way, all the names I used on the spreadsheet are fictitious.
To answer your questions:
a) The data on sheet 1 (Employees) shouldn't need to be changed as the employee's we're copying to sheet 2 and 3 would no longer work for the company. I don't think that once a row is copied to one of the other two sheets, there should be any reason to make a change to it.
b) However, if it's possible to link the two sheets so that, just in case, a change is made to sheet 1 it would also change the 2nd or 3rd sheet. that would be great!
c) If there can be a link (question b) between sheet 1 -2 and 1-3, then if changes needed to be made, I would only make them on sheet 1.

I hope that helps answer the questions you had. Thanks so much for your time!
 
Hi, Sasown!

Give a look at the uploaded file.

It's solved using array formulas (entered with Ctr-Shift-Enter instead of just enter), one formula for each 2nd and 3rd worksheets:
A2: =SI.ERROR(SI(CONTAR.SI(Employees!$C$2:$C$1000;"<>") < FILAS($A$2:A2);“”;INDICE(Employees!A$2:A$1000;K.ESIMO.MENOR(SI(Employees!$C$2:$C$1000<>"";FILA(Employees!$A$2:$A$1000));FILA(A1))));"") -----> in English:
=IFERROR(IF(COUNTIF(Employees!$C$2:$C$1000,"<>") < ROWS($A$2:A2),“”,INDEX(Employees!A$2:A$1000,SMALL(IF(Employees!$C$2:$C$1000<>"",ROW(Employees!$A$2:$A$1000)),ROW(A1)))),"")

The advantage is that you only work on 1st sheet and the others get updated without copying data.

Just advise if any issue.

Regards!
 

Attachments

  • Employee List.xlsx
    23.7 KB · Views: 8
Thanks, I like the conditional formatting on the Employees tab but when I tried to put an X in one of the two columns (C or E), it didn't copy over to one of the other two tabs. Also, the rows on the other tabs don't correspond to the row on the Employee tab that has the X in column C or E or in the case of Jane Doe, she went to Past VT while Joe Smith went (correctly) to Past VFSS.
 
Hi, Sasown!
I mixed things when extracting from my actual file, I'm sorry. Please check again the uploaded file.
Regards!
 

Attachments

  • Employee List.xlsx
    23.3 KB · Views: 11
Hi, Sasown!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
Hi:

If you are interested in Non-array formula.

You can use the following

=IFERROR(IF(COUNTIF(Employees!$C:$C,"<>") < ROWS($A$2:$A2),“”,INDEX(Employees!A$2:A$1000,AGGREGATE(15,6,(ROW(Employees!$A$2:$A$1000)-MIN(ROW(Employees!$A$2:$A$1000))+1)/(Employees!$C$2:$C$1000<>""),ROW(A1)))),"")

Thanks
 
I am interested in this solution. My issue is similar...


I have a workbook with two spreadsheets (1) Current Issues (2) Closed Issues I would like to automatically copy data from the Current Issues spreadsheet and append to the Closed Issues spreadsheet when the issue is closed. The ‘Status’ cell (D) will indicate “Closed”. I do not want to copy the entire row from the Current Issues spreadsheet, just certain cells. Once the copy is completed, I want to delete that row from the Current Issues spreadsheet. The cells I want to copy are as follows:

SEQ - Yes

Submitter - Yes

Title - Yes

Status - Yes

Create Date - Yes

L(arge) S(mall) C- Yes

Assigned To - Yes

Date Assigned - Yes

Days Open - Yes

Date Completed - Yes

Validation No

Row - No

7/28/2017 – No


Test file attached. Can you help?
 

Attachments

  • Test1.xlsx
    54.3 KB · Views: 1
You must log in or register to reply here.
Back
Top