Cross referencing data based on criteria into a new worksheet

[MegEri]

Hello! I am building up a database for a lawn care company - I am trying to set up a workbook that will work as a way to schedule and keep track of our customers services.

What I'm wanting to do is have a different worksheet set up for each program that we offer (5-round, 4-round, 3-round, aeration, mowing, etc.) - basically I would like to have it set up so if I have the "Program" column for a customer set up so it has the different programs, such as "5-Round" or "Mowing" typed in the cell, to then have a function set up to get those customers into a separate "5-Round" or "Mowing" worksheet and, ideally, have it also transfer the other corresponding columns that contain the customers names and addresses.

Any help would be greatly appreciated, hopefully what I explained well enough what I'm trying to do...

 

[jdellasala]

If you put your Orders on one worksheet using a Table (and Data Validation to keep the data as clean as possible),

Book1
	A	B	C	D
1	5-Round		Customer	Program
2	4-Round		Beal	3-Round
3	3-Round		Dinsmor	5-Round
4	Aeration		Bustos	Mowing
5	Mowing		Hanley	3-Round
6			Coggeshall	Mowing
7			Read	4-Round
8			Kendall	Aeration
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=PROPER(TEXTSPLIT("5-round, 4-round, 3-round, aeration, mowing",,", "))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D2:D8	List	=$A$1#

this will work on any other Worksheet in the Workbook:

Book1
	A	B	C	D	E
1	5-Round	4-Round	3-Round	Aeration	Mowing
2	Dinsmor	Read	Beal	Kendall	Bustos
3			Hanley		Coggeshall
Sheet2

Cell Formulas
Range	Formula
A2:B2,D2,E2:E3,C2:C3	A2	=FILTER(Orders[Customer],Orders[Program]=A1,"")

 

[MegEri]

Okay, thank you. What you sent is very close to what I made up already - This is what I have for the main worksheet already, Columns J-N will have the information that I want transferred into the corresponding worksheets (same workbook). In column J, I plan on having '5-round', '4-round', '3-round' typed out for each customer entry. The other columns will have just 'Y' or 'N' for a value.

Customer Contact List.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Company Name	First Name	Last Name	Service Address 1	Service Address 2	Service City	Service State	Service Zip	Lawn Size (ft2)	Program	Aeration?	Irrigation?	Bed Maintenance?	Mowing?
Customer Service Info

Would there be a way to get the customers that have "5-round" in column J to somehow be transferred/linked into the below spreadsheet (in the same workbook) with the corresponding information put into the correct columns? The last two columns will be another formula that I will input once we start in the spring so those columns didn't need to be included, just wanted to have the whole picture here.

Customer Contact List.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Company Name	First Name	Last Name	Service Address 1	Service Address 2	Service City	Service State	Service Zip	Lawn Size (ft2)	Last Service Date	Next Service Date
2
3
4
5
6
5-Round

Is what I'm trying to do, with the sheets formatted the way they are, going to work? Or do I need to start over?