Need a formula to cut rows from a validation list
 

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.
--
Miss Kitty

Need a formula to cut rows from a validation list
 

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" wrote:
...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....As
soon as DNC is selected from the validation list it needs to becut and pasted into
the other worksheet automatically.

Need a formula to cut rows from a validation list
 

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.
--
Miss Kitty


"Bob Bridges" wrote:

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" wrote:
...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....As
soon as DNC is selected from the validation list it needs to becut and pasted into
the other worksheet automatically.

Need a formula to cut rows from a validation list
 

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
on macros I'd rather work with you on the details, if you're willing to take
the time.

--- "Miss Kitty" wrote:
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

--- "Bob Bridges" wrote:
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" wrote:
...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.

Need a formula to cut rows from a validation list
 

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.
--
Miss Kitty


"Bob Bridges" wrote:

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
on macros I'd rather work with you on the details, if you're willing to take
the time.

--- "Miss Kitty" wrote:
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

--- "Bob Bridges" wrote:
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" wrote:
...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.

Need a formula to cut rows from a validation list
 

Let's switch to email, then, if you don't mind. Contact me at
, only without the 'xxx'.

--- "Miss Kitty" wrote:
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" wrote:
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
on macros I'd rather work with you on the details, if you're willing to take
the time.

--- "Miss Kitty" wrote:
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

--- "Bob Bridges" wrote:
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" wrote:
...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.

Need a formula to cut rows from a validation list
 

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.
--
Miss Kitty


"Bob Bridges" wrote:

Let's switch to email, then, if you don't mind. Contact me at
, only without the 'xxx'.

--- "Miss Kitty" wrote:
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" wrote:
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
on macros I'd rather work with you on the details, if you're willing to take
the time.

--- "Miss Kitty" wrote:
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

--- "Bob Bridges" wrote:
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" wrote:
...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.