Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to cut rows from a validation list
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation List/Formula Question | Excel Discussion (Misc queries) | |||
Formula too Long for List Validation | Excel Discussion (Misc queries) | |||
Deleting formula in data validation list | Excel Worksheet Functions | |||
Help required - Data - Validation - List - Formula | New Users to Excel | |||
HELP: Data > Validation ---List ----Formula | Excel Worksheet Functions |