Creating Services with related items

[geno32080]

I have created and invoice for our cleaning services. I would like to use a drop down for the services provided, and associate a price for the service. I got the drop down box going, but am stuck figuring out how to make the bulleted items appear in the next row and how to associate the price with the service. Any ideas? There are Weekly, Monthly, Quarterly, and Yearly Cleaning Services that I would like to set up in the drop down box in cell B14 and then associate what that service description is in starting in Cell 15, and then the price for that service in Cell E14. I put together a data sheet with all the drop down info, but on the invoice sheet , when I choose a service it only shows the single cell. We do weekly cleaning so typing that in every single time is becoming a time consuming process. Any Ideas would be greatly appreciated.

cell b14 cell c15 cell d14 cell e14
Weekly Clean $35.00
•Clean Windows
•Vacuum Floors
• Wipe Down Counter Tops

 

[geno32080]

SOLO CLEANING.xlsm

Shared with Dropbox

www.dropbox.com

Let see if this works,

 

[mumps]

Why are there different drop down lists in B15 to B19? Also there is no drop down in B14. As well, I don't see any quarterly or yearly services. Please clarify.

 

[geno32080]

Some of what you see is experimenting, I started off with the single category of say sweeping, but then thought why not create a category of weekly cleaning that includes sweeping plus a few of the chores needed. B14 is the header of that description, along with unit cost and qty/ hr rate, this is where i can increase the amount for additional rooms, 1 bedroom 2 bedroom etc. I haven't created the quarterly or yearly services yet. I thought if I could get the weekly figured out I would create the rest from what I learned. Does that make sense?

 

[geno32080]

As i get this figured out, I plan on cleaning up the data sheet with the drop down list items, there will be more sub category services to add to the weekly monthly and so on. I so want to work on the Customer info page, I would like to be able to use the drop down to add the customer info to the work order page. Another task, when we get new customers, I want to be able to add the info and it populate on the work order sheet From cell B8, so the address city state zip email information appears on the work order sheet.

 

[mumps]

It is best to design all the features you want because the code will most likely be interrelated. I would suggest that you complete your design and upload an updated version of your workbook with a detailed, step by step description of what you want to do.

 

[geno32080]

Alright then, if that's the best advice. I thought I was furthur along than I am. Thank you for your time.

 

[geno32080]

Mump, thank you for your advice. It really made me start thinking further outside the box. So I kinda started over, this time with a Main Menu with buttons to open different modules, Work Order, one for Customer Information, One for Service Data, and few for future use. Created a folder on the C drive and put sub folders together for the different modules. This gave me the necessary time to think the whole process out. Now that I have all that complete, its time to focus on nuts and bolts of the Work Order.
With that in mind; I really need to create a work order before the invoice. That way I can give this order to the staff with instruction of what the service includes, like a guide line for them, and also it allowed me to create a better hand out for the sales team. This is what we offer and this is what we charge.

Continuing with the Work Order layout, I put together a sample of what the work order should look like along with a service data sheet coded to each type of service, ideally what I would like is cell B14 of the Work Order be the starting point of the services, if I do a drop down box there the user could select the type of service from the service data sheet CELL A2 A3 OR A4, if they choose A2, it would populate all the items associates with cell A2. starting in CELL B15 of the work Order sheet. .
Once I get the Work Order service type figured out, the Bill To, Cell B6 on the work order page is the next hurdle. There is two options to consider, One is existing customers and two, adding new customers. Right now I'm okay with opening the Customer Information sheet and adding new customers, but as the list grows, it could get ugly, I did set up a customer sort button on that sheet just to keep names in alphabetical order. So back to option one, if i put a drop down box, the user could choose a customer and then the rest of the customer info would need to populate in cells B7 B8 B9 and B10 of the Work Order sheet.

Well those are the task I cannot get to play nice, I figured out how to make the drop down list with data validation. Getting the rest of the info to populate is where I'm stuck. Attached is what I got so far, can you give me a hand?

LARL MAIN MENU.xlsm
	B	C	D	E	F	G	H	I	J	K	L
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MAIN MENU

LARL WORK ORDER.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	WORK ORDER
2		WORK ORDER NUMBER	LARLJAX100001
3
4
5		BILL TO:
6		GENE BRYAN		EXTREME CLEANING CO.
7		210A B STREET		940 SCHEIDEL WAY
8		ST AUGUSTINE FL 32080		ST. AUGUSTINE FL 32084
9		904-501-3226		904-439-6389
10		geno607@yahoo.com		larlextremecleaning@gmail.com
11
12		DATE OF SERVICE	3/7/2021 18:23
13		TYPE OF CLEAN	DESCRIPTION OF SERVICE
14		REGULAR CLEANING
15			ALL AREAS OF HOUSE
16			Dust surfaces
17			Dust ceiling fans (within reach)
18			Vacuum carpets
19			Damp mop floors
20			Empty all trash
21			BATHROOM(S)
22			Dust surfaces
23			Dust cabinets, door panels, and baseboards
24			Clean and disinfect surfaces
25			Spot clean cabinet fronts
26			Clean, disinfect, and shine showers and tubs
27			Clean and disinfect toilets inside and out
28			Shine fixtures
29			Vacuum and damp mop floors
30			Empty trash
31			KITCHEN(S)
32			Dust surfaces
33			Dust top of refrigerator
34			Clean and disinfect counter tops
35			Clean and disinfect sink
36			Clean and disinfect microwave inside and out
37			Clean, disinfect, and shine outside of oven and top of range
38			Clean, disinfect, and shine outside of dishwasher
39			Clean, disinfect, and shine outside of refrigerator
40			Clean and disinfect kitchen table
41			Vacuum and damp mop floors
42			Empty trash
43			BEDROOM(S)
44			Dust surfaces
45			Dust and hand wipe furniture tops
46			Vacuum carpets
47			Vacuum and damp mop floors (if non-carpet)
48			Empty trash
49
50
51
52
53
54
55
56
57
WORK ORDER

Cell Formulas
Range	Formula
C12	C12	=NOW()

SERVICE DATA.xlsm
	A	B	C	D	E	F	G	H	I
1	TYPE OF CLEAN	DESCRIPTION OF SERVICE				QUANTITY	A2	A3	A4
2	REGULAR CLEANING	ALL AREAS OF HOUSE	A2	A3	A4	1	$20.00	$30.00	$40.00
3	SPRING CLEANING	Dust surfaces	A2	A3	A4	2
4	MOVE IN / OUT CLEANING	Dust and hand wipe furniture tops		A3	A4	3
5		Dust baseboards, chair rails, and door panels		A3	A4	4
6		Dust ceiling fans (within reach)	A2	A3	A4
7		Vacuum carpets	A2	A3	A4
8		Damp mop floors	A2	A3	A4
9		Dust blinds, window sills, and lock ledges		A3	A4
10		Dust furniture		A3	A4
11		Dust pictures frames		A3
12		Dust lamp and lamp shades		A3
13		Dust and clean mirrors		A3	A4
14		Empty all trash	A2	A3	A4
15		BATHROOMS	A2	A3	A4
16		Dust surfaces	A2	A3	A4
17		Dust blinds, window sills, and lock ledges		A3	A4
18		Dust cabinets, door panels, and baseboards	A2	A3	A4
19		Clean and disinfect surfaces	A2	A3	A4
20		Spot clean cabinet fronts	A2	A3	A4
21		Clean, disinfect, and shine showers and tubs	A2	A3	A4
22		Clean and disinfect toilets inside and out	A2	A3	A4
23		Clean and disinfect door knobs and switch plates		A3	A4
24		Shine fixtures	A2	A3	A4
25		Vacuum and damp mop floors	A2	A3	A4
26		Empty trash	A2	A3	A4
27		KITCHEN	A2	A3	A4
28		Dust surfaces	A2	A3	A4
29		Dust blinds, window sills, and lock ledges		A3	A4
30		Dust chair rails, cabinets, door panels, and baseboards		A3	A4
31		Dust top of refrigerator	A2	A3	A4
32		Clean and disinfect counter tops	A2	A3	A4
33		Spot clean cabinet fronts		A3	A4
34		Clean and disinfect door knobs and switch plates		A3	A4
35		Clean and disinfect sink	A2	A3	A4
36		Clean and disinfect microwave inside and out	A2	A3	A4
37		Clean, disinfect, and shine outside of oven and top of range	A2	A3	A4
38		Clean, disinfect, and shine outside of dishwasher	A2	A3	A4
39		Clean, disinfect, and shine outside of refrigerator	A2	A3	A4
40		Clean and disinfect kitchen table	A2	A3	A4
41		Vacuum and damp mop floors	A2	A3	A4
42		Empty trash	A2	A3	A4
43		BEDROOMS	A2	A3	A4
44		Dust surfaces	A2	A3	A4
45		Dust and hand wipe furniture tops	A2	A3	A4
46		Dust furniture		A3	A4
47		Dust baseboards, chair rails, and door panels		A3	A4
48		Dust blinds, window sills, and lock ledges		A3	A4
49		Vacuum carpets	A2	A3	A4
50		Vacuum and damp mop floors (if non-carpet)	A2	A3	A4
51		Change sheets (upon request)
52		Make beds (upon request)
53		Empty trash	A2	A3	A4
54		ADDITIONAL SERVICES
55		Oven Clean
56		Carpet Shampoo (Per Room)
57		Wash Outside Windows (Per Window)
58		Pressure Wash House (Per Square Foot)
59		Clean Driveway and Sidewalks (Per Square Foot)
SERVICE DATA

Cells with Data Validation
Cell	Allow	Criteria
A9	List	=$B$10

 

[geno32080]

That first sheet was the main menu, the x12bb doesnt copy the icons from the main menu, sorry

 

[mumps]

I think I've got the first part figured out. Click here to download your file. In the WORK ORDER sheet you don't need to click any buttons. Just make a selection in cell B14 and the services will be listed automatically starting in C15. I will need the Customer information sheet in order to take care of that part of your request. Add that sheet to the workbook I attached and then upload a copy of the file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. It is much easier to work with the workbook rather than screenshots.

 

[geno32080]

Thanks, looks like its going in the right direction. However, when I changed the service I noticed at the bottom it would show 34 of 58, when regular cleaning was selected, then it changed all the a2 a3 a4 on the data sheet where every category read a2 a3 a4? Attached is the sheet with the customer info page added.

geno (2).xlsm

Shared with Dropbox

www.dropbox.com