I have attached a sample file to show our current process:
Worksheet 1: a static inventory list
Worksheet 2: a tracking tool for select procedures from Worksheet 1 that have revisions
In Worksheet 1, inserting "Yes" in column "X" triggers 6 cells from that row to be copied and pasted into Worksheet 2 into the next available blank row, as follows:
Column V copied into Column A
Column W copied into Column B
Column A copied into Column C
Column B copied into Column D
Column L copied into Column E
Column M copied into Column F
There are instances when the same row needs to be copied over to Worksheet 2 a second, third and fourth time (as there can be multiple revisions to the same procedure during the 2-month production cycle), which requires the user to go back to Worksheet 1 and remove and re-insert "Yes" in Column "X" each time.
I am looking for a more sophisticated solution where:
Users don't have to go back and forth between 2 spreadsheets, but can work exclusively in Worksheet 2
Users have a means from within Worksheet 2 to find and select rows from Worksheet 1, based on selecting the values for up to 3 columns in Worksheet 1 (Column A, B and D)
That from the selected rows, only specific cells are copied & pasted (as "values") into Worksheet 2 (as originally outlined above).