Moving Info from two Cells to another worksheet

[crawl22kp]

Hey all,

I keep thinking I can figure things out but the learning curve is real. Here is the situation.

Have a column for first names and on for last names on excel sheet. I have a function that will "link" both cells to one cell on a different sheet. If I change things though on the first sheet, since they are linked cells between the two sheets, the second sheet changes also.

What I want is to have those two cells of info (first and last name cells) to auto populate to the one cell on the other sheet in one column like as the list of names populates and not be linked. Then, they only populate on the second sheet depending on the value status of another cell. When the second sheet list is populated, I want it to where when I set filters to moves the rows around it won't affect or change anything on the first sheet.

Example:

Column A has status changes Go, maybe, No-Go

Column B is last name input

Column C is first name input

Parker, John passed a test and is a "Go" so I change his status in column A. His name populates from "column B and C Sheet 1" to "column A Sheet 2" without being linked.

So if I change the status to something else or delete the information on sheet 1 nothing changes on sheet 2. If I change the rows around from filtering on sheet 1, nothing changes on sheet 2 as far as order or data. I just want the names to be populated into a one cell, in a column list, onto sheet 2 in column A.

Thank you much all you geniuses if there is something out there to make this happen.

Kurt

 

[rpaulson]

Kurt,

try this.

right click on sheet1 and - select view code.
paste this in the text box.

Private Sub Worksheet_Change(ByVal Target As Range)

   'Do nothing if more than one cell is changed or content deleted

   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
   If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
        If UCase(Target.Value) = "GO" Then
            r = Target.Row
            With Worksheets("Sheet2")
            lr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
            .Cells(lr, "A") = Cells(r, "C") & ", " & Cells(r, "B")
            End With
        End If
   End If
End Sub

when you enter the word go into sheet one column A, then the code should paste the name from columns B and C to the next row in sheet 2 column A

hth,

Ross

 

[crawl22kp]

That was absolutely amazing. Worked like a charm! You guys are on God status with this stuff.

I appreciate the help.

Kurt

 

[rpaulson]

Thanks for the feedback.