Order Form with email and create new functions

COGICPENNY

New Member
Joined
Feb 16, 2009
Messages
44
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
ABDEFGHIJKL
1RESTAURANT ORDER FORM
2ORDER NO:
3ORDER DATE:
4ORDERED BY:
5Breakfast (7 am - 8 am)
6DELIVERY DATE:Lunch (10 am - 11 am)
7DELIVERY TIME:Dinner (3 pm - 4 pm)
8POWER CHANGES LIVES, INC.DELIVERY TYPE:
9403 WELSH PLACE
10MORRIS PLAINS, NJ 07950-2954INVOICE NO:
11(973) 866-5531INVOICE DATE:
12www.powerchangeslives.com
13
14ORGANIZATION NAME:BECK'S BIRTHING CENTERCONTACT NAME:JANE DOE
15ADDRESS:200 MADISON AVE
16CITY:MORRISTOWNSTATE: NJZIP:07950
17PHONE:(973) 444-4444AWARD AMOUNT (MEALS):7,950AWARD BALANCE:
18
19RESTAURANT NAME:Jake's BBQ and GrillRESTAURANT STATUS:APPROVED
20ADDRESS:1234 Anywhere Street
21CITY:AnywhereSTATE: NJZIP:11111
22PHONE:(973) 555-1212
23
24ITEM NO.RESTAURANT NAMEMEAL DESCRIPTIONSERVING SIZEQTYUNIT PRICETOTAL COSTTOTAL MEALS
251051Jake's BBQ and GrillHamburgers and Fries1150$ 10.00$ 1,500.00150
26
27
28
29
30
31TOTAL MEALS150.00
32TOTAL INVOICE$ 1,500.00
33PLACE ORDERNEW ORDER
34
ORDER FORM
Cell Formulas
RangeFormula
D14D14=VLOOKUP(B17,'ORG LIST'!A:B,2,FALSE)
G14G14=VLOOKUP(B17,'ORG LIST'!A:C,3,FALSE)
B15B15=VLOOKUP(B17,'ORG LIST'!A:D,4,FALSE)
B16B16=VLOOKUP(B17,'ORG LIST'!A:E,5,FALSE)
G16G16=VLOOKUP(B17,'ORG LIST'!A:G,7,FALSE)
E17E17=VLOOKUP(B17,'ORG LIST'!A:H,7,FALSE)
D19D19=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:B,2,FALSE)
G19G19=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:F,6,FALSE)
B20B20=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:C,3,FALSE)
B21B21=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:D,4,FALSE)
G21G21=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:E,5,FALSE)
B25B25=VLOOKUP(A25,'MENU ITEMS'!A:B,2,FALSE)
D25D25=VLOOKUP(A25,'MENU ITEMS'!A:F,6,FALSE)
E25E25=VLOOKUP(A25,'MENU ITEMS'!A:G,7,FALSE)
H25H25=SUM(G25*F25*E25)
I25I25=SUM(E25*F25)
G25:G30G25=IFNA(VLOOKUP(A25,'MENU ITEMS'!A:I,9,FALSE)," ")
I31I31=SUM(I25:I30)
I32I32=SUM(H25:H30)
Named Ranges
NameRefers ToCells
'MENU ITEMS'!_FilterDatabase='MENU ITEMS'!$A$1:$I$9B25, D25:E25, G25:G30
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B25:B30Expression=$C$25="NO"textNO
B26:B30Expression=$C$25="NO"textNO
B25:B30Expression=$C$25="#N/A"textNO
 

Attachments

  • PROJECT F.E.E.D. RESTUARANT ORDER FORM.JPG
    PROJECT F.E.E.D. RESTUARANT ORDER FORM.JPG
    92.1 KB · Views: 10
  • PROJECT F.E.E.D. ORDER FORM - TASK SCREENSHOT.JPG
    PROJECT F.E.E.D. ORDER FORM - TASK SCREENSHOT.JPG
    165.2 KB · Views: 10

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
Due to the urgency of this request, I've cross-posted it at the following link:
 
Upvote 0
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
ABDEFGHIJKL
1RESTAURANT ORDER FORM
2ORDER NO:
3ORDER DATE:
4ORDERED BY:
5Breakfast (7 am - 8 am)
6DELIVERY DATE:Lunch (10 am - 11 am)
7DELIVERY TIME:Dinner (3 pm - 4 pm)
8POWER CHANGES LIVES, INC.DELIVERY TYPE:
9403 WELSH PLACE
10MORRIS PLAINS, NJ 07950-2954INVOICE NO:
11(973) 866-5531INVOICE DATE:
12www.powerchangeslives.com
13
14ORGANIZATION NAME:BECK'S BIRTHING CENTERCONTACT NAME:JANE DOE
15ADDRESS:200 MADISON AVE
16CITY:MORRISTOWNSTATE: NJZIP:07950
17PHONE:(973) 444-4444AWARD AMOUNT (MEALS):7,950AWARD BALANCE:
18
19RESTAURANT NAME:Jake's BBQ and GrillRESTAURANT STATUS:APPROVED
20ADDRESS:1234 Anywhere Street
21CITY:AnywhereSTATE: NJZIP:11111
22PHONE:(973) 555-1212
23
24ITEM NO.RESTAURANT NAMEMEAL DESCRIPTIONSERVING SIZEQTYUNIT PRICETOTAL COSTTOTAL MEALS
251051Jake's BBQ and GrillHamburgers and Fries1150$ 10.00$ 1,500.00150
26
27
28
29
30
31TOTAL MEALS150.00
32TOTAL INVOICE$ 1,500.00
33PLACE ORDERNEW ORDER
34
ORDER FORM
Cell Formulas
RangeFormula
D14D14=VLOOKUP(B17,'ORG LIST'!A:B,2,FALSE)
G14G14=VLOOKUP(B17,'ORG LIST'!A:C,3,FALSE)
B15B15=VLOOKUP(B17,'ORG LIST'!A:D,4,FALSE)
B16B16=VLOOKUP(B17,'ORG LIST'!A:E,5,FALSE)
G16G16=VLOOKUP(B17,'ORG LIST'!A:G,7,FALSE)
E17E17=VLOOKUP(B17,'ORG LIST'!A:H,7,FALSE)
D19D19=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:B,2,FALSE)
G19G19=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:F,6,FALSE)
B20B20=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:C,3,FALSE)
B21B21=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:D,4,FALSE)
G21G21=VLOOKUP(B22,'APPROVED RESTAURANTS'!A:E,5,FALSE)
B25B25=VLOOKUP(A25,'MENU ITEMS'!A:B,2,FALSE)
D25D25=VLOOKUP(A25,'MENU ITEMS'!A:F,6,FALSE)
E25E25=VLOOKUP(A25,'MENU ITEMS'!A:G,7,FALSE)
H25H25=SUM(G25*F25*E25)
I25I25=SUM(E25*F25)
G25:G30G25=IFNA(VLOOKUP(A25,'MENU ITEMS'!A:I,9,FALSE)," ")
I31I31=SUM(I25:I30)
I32I32=SUM(H25:H30)
Named Ranges
NameRefers ToCells
'MENU ITEMS'!_FilterDatabase='MENU ITEMS'!$A$1:$I$9B25, D25:E25, G25:G30
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B25:B30Expression=$C$25="NO"textNO
B26:B30Expression=$C$25="NO"textNO
B25:B30Expression=$C$25="#N/A"textNO
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top