Move row to new excel sheet when cell criteria updated - Please help ASAP

%3CLINGO-SUB%20id%3D%22lingo-sub-1616070%22%20slang%3D%22en-US%22%3EMove%20row%20to%20new%20excel%20sheet%20when%20cell%20criteria%20updated%20-%20Please%20help%20ASAP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616070%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20trying%20to%20do%20this%20for%20the%20past%204%20hours%20but%20everything%20I've%20tried%20either%20doesn't%20work%20or%20doesn't%20make%20sense%20to%20me%20so%20I'm%20desperate%20for%20help%20now.%20I've%20tried%20formulas%20and%20VBAs%20and%20I'm%20at%20my%20wit's%20end.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBackground%3A%20For%20work%20I%20get%20allocated%20cases%20which%20I%20then%20make%20active%20once%20I've%20spoken%20to%20the%20service%20user%2C%20and%20then%20close%20when%20all%20work%20is%20carried%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%3A%20I%20want%20to%20be%20able%20to%20update%20a%20cell%20from%20%22new%22%20to%20%22active%22%2C%20and%20have%20that%20move%20the%20row%20from%20the%20%22New%22%20sheet%20to%20the%20%22Active%22%20sheet.%20I%20then%20want%20the%20same%20to%20happen%20when%20I%20close%20a%20case%3B%20update%20the%20cell%20and%20move%20the%20row%20from%20%22Active%22%20sheet%20to%20%22Closed%22%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20so%20much%20easier%20if%20I%20could%20just%20write%2C%20%22IF%20cell%20in%20column%20F%3Dactive%2C%20move%20row%20to%20Active%20sheet%22%20but%20instead%20Excel%20formulas%20are%20so%20complicated%20that%20I%20don't%20understand%20what%20each%20bit%20is%20asking%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20can%20someone%20explain%20it%20like%20I'm%205%20and%20make%20it%20straightforward%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1616070%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1616870%22%20slang%3D%22en-US%22%3ERe%3A%20Move%20row%20to%20new%20excel%20sheet%20when%20cell%20criteria%20updated%20-%20Please%20help%20ASAP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F773169%22%20target%3D%22_blank%22%3E%40riva85%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3EPlease%20can%20someone%20explain%20it%20like%20I'm%205%20and%20make%20it%20straightforward%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20I'd%20rather%20approach%20you%20as%20if%20you're%2C%20oh%2C%2045%2C%20and%20willing%20to%20re-think%20your%20design.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20have%20in%20mind%20is%20a%20single%20database%20(i.e.%2C%20a%20single%20worksheet)%20on%20which%20ALL%20rows%20for%20ALL%20cases%20are%20retained.%20Each%20row%20carries%20a%20status%20indicator--%22Active%22%2C%20%22Closed%22%2C%20%22Limbo%22%2C%22whatever%22--and%20then%20there's%20a%20single%20%22Dashboard%22%20or%20output%20sheet%20of%20some%20kind%2C%20on%20which%20you%20can%20request%20at%20any%20time%20a%20full%20accounting%20of%20all%20%22Active%22%20sheets%20or%20all%20of%20any%20class%20that%20you%20have.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20approach%20would%20(I%20will%20submit)%20meet%20your%20goals%20AND%20take%20advantage%20of%20Excel's%20amazing%20abilities%20to%20take%20a%20single%20data%20table%20and%20extract%20all%20kinds%20of%20useful%20reports.%20I've%20just%20scratched%20the%20surface%20on%20what%20I%20wrote%20above.%20Would%20you%20like%2C%20for%20example%2C%20statistical%20summaries%20on%20how%20many%20cases%20have%20been%20handled%20per%20month%3F%20How%20about%20how%20many%20involve%20issues%20X%20and%20Y%2C%20to%20say%20nothing%20of%20Z%3F%20The%20Pivot%20Table%20is%20at%20your%20disposal%20for%20that%20kind%20of%20report%2C%20and%20it%20would%20do%20it%20all%20from%20that%20single%20database.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have been trying to do this for the past 4 hours but everything I've tried either doesn't work or doesn't make sense to me so I'm desperate for help now. I've tried formulas and VBAs and I'm at my wit's end.

 

Background: For work I get allocated cases which I then make active once I've spoken to the service user, and then close when all work is carried out.

 

Problem: I want to be able to update a cell from "new" to "active", and have that move the row from the "New" sheet to the "Active" sheet. I then want the same to happen when I close a case; update the cell and move the row from "Active" sheet to "Closed" sheet.

 

It would be so much easier if I could just write, "IF cell in column F=active, move row to Active sheet" but instead Excel formulas are so complicated that I don't understand what each bit is asking

Please can someone explain it like I'm 5 and make it straightforward?

Thank you

1 Reply

@riva85 

 

You wrote: Please can someone explain it like I'm 5 and make it straightforward?

 

Well, I'd rather approach you as if you're, oh, 45, and willing to re-think your design.

 

What I have in mind is a single database (i.e., a single worksheet) on which ALL rows for ALL cases are retained. Each row carries a status indicator--"Active", "Closed", "Limbo","whatever"--and then there's a single "Dashboard" or output sheet of some kind, on which you can request at any time a full accounting of all "Active" sheets or all of any class that you have.

 

That approach would (I will submit) meet your goals AND take advantage of Excel's amazing abilities to take a single data table and extract all kinds of useful reports. I've just scratched the surface on what I wrote above. Would you like, for example, statistical summaries on how many cases have been handled per month? How about how many involve issues X and Y, to say nothing of Z? The Pivot Table is at your disposal for that kind of report, and it would do it all from that single database.