Excel Function Question

[Kasturi]

I am needing to know if there is an Excel function out there that will copy over the contents of one cell into another by what is typed in another cell. Did that make sense?

Lets say that I have info in


(A1) John (B1) Smith (C1)X (D1)


Now I want to be able to copy over A1 & B1 to another part of the spreadsheet/workbook if I were to type the words "leave" into D1.


So basically it would look something like this...

(A1)John (B1)Smith (C1)X (D1)leave (want info to go here---->) (M4)John (N4)Smith (O4)currently on leave

And then have it automatically add the names below the one in M4-O4. so it would add names in M5-O5, M6-O6, etc

I have this spread sheet that has over 1000 names on it & it would help out alot if I could just use a function that could copy the information to another part of the spreadsheet or another workbook.

I know that I can use the filter, but I may want to have this stuff on another worksheet alltogether.


Also, if this is possible, is there a way that it can be automatically removed if I were to delete the word "leave" from the cell?


If this has not made any sense let me know & I will try to explain it better.

Thanks
Kas

[Force Flow]

You'll probably want to look at "if statements"

Attached is what I did according to your description.

The $ sign in front of the cell letters and numbers means that the letter or number will not change if copied/dragged somewhere else.