Automatically copying a row based on criteria

[ngmanager]

Hello everyone,

I am looking to use a series of sheets to sort and track caseloads for my team, as well as some other data. I know how to set up a sheet so that I can sort or search, but i have a vision of what I'm wanting that I am hoping for help with:

Ideally, I would like to input all information into one sheet, and then have rows copied based on criteria. For example: I have 5 workers. Every client I input into Sheet 1 will have 1 worker. I would like to be able to enter the client with all of their information into sheet 1, including the name of their worker. If I put in their worker's name - say "Janet"- I would then like that row (or even just their name and file number) to transfer to a separate sheet that is titled "Janet." And so on for the 4 other workers.

My hope is to have a master document with all cases, and then be able to flip through sheets to see only each worker's caseload, and to not have to manually copy a row each time it gets assigned into the worker's sheet. I am also hoping that this function will update both if I change information in the row on the master document, and when I add new people.

I have tried a bunch of stuff, and am not happy with any of the results. The closest I have gotten is being able to copy information based on criteria, however the row shows up in the identical row on the worker's sheet, meaning that at times there are like 15 rows between clients.

Thanks for your help!

 

[kevin9999]

I note you have 365, so the Filter function should be available to you. Have you considered using Filter() to achieve what (I think) you're after? The set up would look something like that below. On a master sheet, set up your data however you please. Then on each "workers" sheet, you'd only need to put a single formula in cell A2 (for example) to pick up all the clients assigned to whatever worker's name you put into that sheets formula - and it would automatically update as you added/deleted/changed any data on the Master sheet.
In the demo below, I made the Filter just go down to row 50 - make it as big (as many rows) as you think you'd ever need.

Master sheet (example)

Book1
	A	B	C	D	E
1	Client name	Client number	Assigned to	Other info	Other info
2	Bill Smith	1	Janet	Bill Smith info A	Bill Smith info B
3	Sue Brown	2	John	Sue Brown info A	Sue Brown info B
4	Jack Spratt	3	Janet	Jack Spratt info A	Jack Spratt info B
5	Holly Christmas	4	John	Holly Christmas info A	Holly Christmas info B
6
Master

'Janet' sheet (example)

Book1
	A	B	C	D	E
1	Client name	Client number	Assigned to	Other info	Other info
2	Bill Smith	1	Janet	Bill Smith info A	Bill Smith info B
3	Jack Spratt	3	Janet	Jack Spratt info A	Jack Spratt info B
4
Janet

Cell Formulas
Range	Formula
A2:E3	A2	=FILTER(Master!$A$2:$E$50,Master!$C$2:$C$50="Janet","")
Dynamic array formulas.

'John' sheet (example)

Book1
	A	B	C	D	E
1	Client name	Client number	Assigned to	Other info	Other info
2	Sue Brown	2	John	Sue Brown info A	Sue Brown info B
3	Holly Christmas	4	John	Holly Christmas info A	Holly Christmas info B
4
John

Cell Formulas
Range	Formula
A2:E3	A2	=FILTER(Master!$A$2:$E$50,Master!$C$2:$C$50="John","")
Dynamic array formulas.