Need a formula to cut rows from a validation list

[Miss Kitty]

I have text data - when a customer no longer wants to be contacted I would
like the rows associated with it to cut and paste into a sepearte worksheet
automatically. This is mainly determined from a validation list that is
specified; DNC, Recruited, New... I am wondering even if this can be done.
e.g
COlB ColF ColG ColH (you get the picture)
DNC Fred Smith 123 Street Anywhere 123 456 1-888-000
As soon as DNC is selected from the validation list it needs to be cut and
pasted into the other worksheet automatically.

Any suggestions or solutions are very much appreciated.

 

[Bob Bridges]

I think you can easily enough mark a row "no longer to be contacted", and
have your lookup functions etc properly ignore those rows until you can get
around to moving them somewhere. I can also think of a way with formulae to
create a list of all the customer rows so marked, though it's a bit kludgy.
But I don't see a way to do the actual relocation of that data with worksheet
formulae; I'd use a macro for that.

 

[Miss Kitty]

Hey Bob,
Thanks for your prompt reply.
I am not that great with Macros. Would you happen to have a solution for me?
I also have to cut specific information from the list in Sheet1 that I would
need to paste into Sheet2.
What I have at the moment is:

With Sheets("Sheet1")
.Range("A2:L15").Cut Destination:=Sheets("Sheet2").Range("A1")
.Range("CA2:IV11").Cout Destination:=Sheets("Sheet1").Range("CA7")
End With

Any help would be appreciated.

 

[Bob Bridges]

Hey, Miss Kitty. I don't have a solution at hand; we'll have to work it out
together. But I'm a teacher by motivation; I wouldn't consent to just hand
you the solution even if I had it already written. What I'll do is explain
the bits of it to you, one at a time, until you understood them well enough
to write it yourself. "Teach a man to fish", and all that, you know. If
you're willing to take some time to learn it, I'll MAKE you that good with
macros ...if you want to be.

If not, I think you're on the right track. The way to make it automatic,
eventually, is to write a Worksheet_Change subroutine, which fires
automatically whenever the user changes ANY cell (not just the one that marks
a customer as do-not-contact); you'd write to see whether this is that type
of change, and if it is move the row elsewhere. But if you're not that great

 

[Miss Kitty]

Hey Bob,
I'm game if you have a little time. I can get it to go so far but then it
cuts the entire cells in those rows & columns, not what my desired outcome
was suppose to be. So this is probably an obvious place for me to start. Thx
for your help.

 

[Bob Bridges]

Let's switch to email, then, if you don't mind. Contact me at
(e-mail address removed), only without the 'xxx'.

 

[Miss Kitty]

Hey Bob,
I think I have found my solution. After a few hrs of re-dos I think I have
found what I needed. Thx muchly for your help and offer. Unfortunately I have
to have this in before the end of the week.
Thx to all who contributed.