Order Form with email and create new functions

[COGICPENNY]

I started a non-profit to help those in low-income, minority and underserved communities. One of my projects is Project F.E.E.D. (Friends Ensuring Every Dinner) that addresses food-insecurity. I was recently awarded a grant from my State to provide 90,000 meals throughout NJ to those food-insecure and to fight hunger. I do not have the funds to purchase an ERP system, and need to create an ordering system. I have partnered (awarded) other non-profits throughout NJ in order to expand the reach of this program to feed more individuals and families than I could on my own. Each partner (site) would place orders that would go to a partnering restaurant to make meals and provide to the sites.

The form that I've tried to create would have some user inputs, vlookups for menu items, organization sites, and restaurant partners to place orders. It will also track the available meal balance they can order from their original meal awards. And finally, it will allow them to click a button to "PLACE ORDER" (brings up email) and "CREATE NEW ORDER" (creates new worksheet to begin the process again).

I'm not sure if this type of functionality even exists, but I know that the SMARTEST PEOPLE who would know is right here in this forum. You've helped me out of tough situations before, and I'm looking for that same help again -- YOU ROCK AND THANK YOU IN ADVANCE.

In the first tab "INSTRUCTIONS" I provided step-by-step tasks that outline how the functionality of the form would operate. In the tab "ORDER FORM" I've already created the Master Order form with sample VLOOKUPS for the data inputs. The file link is: PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsx

We launch this program this week and I'm hoping to train our sites on this form within the next 1-3 days.

Thanks,
Penny

Here's the code for the minisheet:

PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsx
	A	B		D	E	F	G	H	I	J	K	L
1					RESTAURANT ORDER FORM
2					ORDER NO:
3					ORDER DATE:
4					ORDERED BY:
5											Breakfast (7 am - 8 am)
6					DELIVERY DATE:						Lunch (10 am - 11 am)
7					DELIVERY TIME:						Dinner (3 pm - 4 pm)
8	POWER CHANGES LIVES, INC.				DELIVERY TYPE:
9	403 WELSH PLACE
10	MORRIS PLAINS, NJ 07950-2954				INVOICE NO:
11	(973) 866-5531				INVOICE DATE:
12	www.powerchangeslives.com
13
14	ORGANIZATION NAME:			BECK'S BIRTHING CENTER	CONTACT NAME:		JANE DOE
15	ADDRESS:	200 MADISON AVE
16	CITY:	MORRISTOWN		STATE: NJ		ZIP:	07950
17	PHONE:	(973) 444-4444		AWARD AMOUNT (MEALS):	7,950	AWARD BALANCE:
18
19	RESTAURANT NAME:			Jake's BBQ and Grill	RESTAURANT STATUS:		APPROVED
20	ADDRESS:	1234 Anywhere Street
21	CITY:	Anywhere		STATE: NJ		ZIP:	11111
22	PHONE:	(973) 555-1212
23
24	ITEM NO.	RESTAURANT NAME		MEAL DESCRIPTION	SERVING SIZE	QTY	UNIT PRICE	TOTAL COST	TOTAL MEALS
25	1051	Jake's BBQ and Grill		Hamburgers and Fries	1	150	$ 10.00	$ 1,500.00	150
26
27
28
29
30
31							TOTAL MEALS		150.00
32							TOTAL INVOICE		$ 1,500.00
33		PLACE ORDER		NEW ORDER
34
ORDER FORM

Cell Formulas
Range	Formula
D14	D14	=VLOOKUP(B17,'ORG LIST'!A:B,2,FALSE)
G14	G14	=VLOOKUP(B17,'ORG LIST'!A:C,3,FALSE)
B15	B15	=VLOOKUP(B17,'ORG LIST'!A:D,4,FALSE)
B16	B16	=VLOOKUP(B17,'ORG LIST'!A:E,5,FALSE)
G16	G16	=VLOOKUP(B17,'ORG LIST'!A:G,7,FALSE)
E17	E17	=VLOOKUP(B17,'ORG LIST'!A:H,7,FALSE)
D19	D19	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:B,2,FALSE)
G19	G19	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:F,6,FALSE)
B20	B20	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:C,3,FALSE)
B21	B21	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:D,4,FALSE)
G21	G21	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:E,5,FALSE)
B25	B25	=VLOOKUP(A25,'MENU ITEMS'!A:B,2,FALSE)
D25	D25	=VLOOKUP(A25,'MENU ITEMS'!A:F,6,FALSE)
E25	E25	=VLOOKUP(A25,'MENU ITEMS'!A:G,7,FALSE)
H25	H25	=SUM(G25*F25*E25)
I25	I25	=SUM(E25*F25)
G25:G30	G25	=IFNA(VLOOKUP(A25,'MENU ITEMS'!A:I,9,FALSE)," ")
I31	I31	=SUM(I25:I30)
I32	I32	=SUM(H25:H30)

Named Ranges
Name	Refers To	Cells
'MENU ITEMS'!_FilterDatabase	='MENU ITEMS'!$A$1:$I$9	B25, D25:E25, G25:G30

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B25:B30	Expression	=$C$25="NO"	text	NO
B26:B30	Expression	=$C$25="NO"	text	NO
B25:B30	Expression	=$C$25="#N/A"	text	NO

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Interactive order form
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[COGICPENNY]

Due to the urgency of this request, I've cross-posted it at the following link:

Create Interactive Order Form (VLOOKUP, Email Function, Calculate Balance)

I started a non-profit to help those in low-income, minority and underserved communities. One of my projects is Project F.E.E.D. (Friends Ensuring Every Dinner) that addresses food-insecurity. I was recently awarded a grant from my State to provide 90,000 meals throughout NJ to those...

www.excelforum.com

 

[Jazz Engineer]

I started a non-profit to help those in low-income, minority and underserved communities. One of my projects is Project F.E.E.D. (Friends Ensuring Every Dinner) that addresses food-insecurity. I was recently awarded a grant from my State to provide 90,000 meals throughout NJ to those food-insecure and to fight hunger. I do not have the funds to purchase an ERP system, and need to create an ordering system. I have partnered (awarded) other non-profits throughout NJ in order to expand the reach of this program to feed more individuals and families than I could on my own. Each partner (site) would place orders that would go to a partnering restaurant to make meals and provide to the sites.

The form that I've tried to create would have some user inputs, vlookups for menu items, organization sites, and restaurant partners to place orders. It will also track the available meal balance they can order from their original meal awards. And finally, it will allow them to click a button to "PLACE ORDER" (brings up email) and "CREATE NEW ORDER" (creates new worksheet to begin the process again).

I'm not sure if this type of functionality even exists, but I know that the SMARTEST PEOPLE who would know is right here in this forum. You've helped me out of tough situations before, and I'm looking for that same help again -- YOU ROCK AND THANK YOU IN ADVANCE.

In the first tab "INSTRUCTIONS" I provided step-by-step tasks that outline how the functionality of the form would operate. In the tab "ORDER FORM" I've already created the Master Order form with sample VLOOKUPS for the data inputs. The file link is: PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsx

We launch this program this week and I'm hoping to train our sites on this form within the next 1-3 days.

Thanks,
Penny

Here's the code for the minisheet:

PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsx
	A	B		D	E	F	G	H	I	J	K	L
1					RESTAURANT ORDER FORM
2					ORDER NO:
3					ORDER DATE:
4					ORDERED BY:
5											Breakfast (7 am - 8 am)
6					DELIVERY DATE:						Lunch (10 am - 11 am)
7					DELIVERY TIME:						Dinner (3 pm - 4 pm)
8	POWER CHANGES LIVES, INC.				DELIVERY TYPE:
9	403 WELSH PLACE
10	MORRIS PLAINS, NJ 07950-2954				INVOICE NO:
11	(973) 866-5531				INVOICE DATE:
12	www.powerchangeslives.com
13
14	ORGANIZATION NAME:			BECK'S BIRTHING CENTER	CONTACT NAME:		JANE DOE
15	ADDRESS:	200 MADISON AVE
16	CITY:	MORRISTOWN		STATE: NJ		ZIP:	07950
17	PHONE:	(973) 444-4444		AWARD AMOUNT (MEALS):	7,950	AWARD BALANCE:
18
19	RESTAURANT NAME:			Jake's BBQ and Grill	RESTAURANT STATUS:		APPROVED
20	ADDRESS:	1234 Anywhere Street
21	CITY:	Anywhere		STATE: NJ		ZIP:	11111
22	PHONE:	(973) 555-1212
23
24	ITEM NO.	RESTAURANT NAME		MEAL DESCRIPTION	SERVING SIZE	QTY	UNIT PRICE	TOTAL COST	TOTAL MEALS
25	1051	Jake's BBQ and Grill		Hamburgers and Fries	1	150	$ 10.00	$ 1,500.00	150
26
27
28
29
30
31							TOTAL MEALS		150.00
32							TOTAL INVOICE		$ 1,500.00
33		PLACE ORDER		NEW ORDER
34
ORDER FORM

Cell Formulas
Range	Formula
D14	D14	=VLOOKUP(B17,'ORG LIST'!A:B,2,FALSE)
G14	G14	=VLOOKUP(B17,'ORG LIST'!A:C,3,FALSE)
B15	B15	=VLOOKUP(B17,'ORG LIST'!A:D,4,FALSE)
B16	B16	=VLOOKUP(B17,'ORG LIST'!A:E,5,FALSE)
G16	G16	=VLOOKUP(B17,'ORG LIST'!A:G,7,FALSE)
E17	E17	=VLOOKUP(B17,'ORG LIST'!A:H,7,FALSE)
D19	D19	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:B,2,FALSE)
G19	G19	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:F,6,FALSE)
B20	B20	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:C,3,FALSE)
B21	B21	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:D,4,FALSE)
G21	G21	=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:E,5,FALSE)
B25	B25	=VLOOKUP(A25,'MENU ITEMS'!A:B,2,FALSE)
D25	D25	=VLOOKUP(A25,'MENU ITEMS'!A:F,6,FALSE)
E25	E25	=VLOOKUP(A25,'MENU ITEMS'!A:G,7,FALSE)
H25	H25	=SUM(G25*F25*E25)
I25	I25	=SUM(E25*F25)
G25:G30	G25	=IFNA(VLOOKUP(A25,'MENU ITEMS'!A:I,9,FALSE)," ")
I31	I31	=SUM(I25:I30)
I32	I32	=SUM(H25:H30)

Named Ranges
Name	Refers To	Cells
'MENU ITEMS'!_FilterDatabase	='MENU ITEMS'!$A$1:$I$9	B25, D25:E25, G25:G30

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B25:B30	Expression	=$C$25="NO"	text	NO
B26:B30	Expression	=$C$25="NO"	text	NO
B25:B30	Expression	=$C$25="#N/A"	text	NO

I am a former database programmer and spreadsheet programmer. I can’t remember how many times I seen users create a database application in a spreadsheet. I know when an application requires a database programmer and your situation is definitely one of those situations. Your requirements aren’t that complicated, but the extensive use of lookups, which often also cause automatic calculations, is a classic database application. I’m pretty confident this could be done in Access. If COGIC means Church of God in Christ, you should be able to find a good database programmer who would do this on a volunteer or low cost basis. I warn you, Excel is the wrong tool for your needs. You would save so much time that you could justify taking funds from your budget. Your approach is a classic mis-use of a spreadsheet. Best wishes, Jazz Engineer.