Order Form with email and create new functions

COGICPENNY

New Member
Joined
Feb 16, 2009
Messages
42
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: 3
  • PROJECT F.E.E.D. ORDER FORM - TASK SCREENSHOT.JPG
    PROJECT F.E.E.D. ORDER FORM - TASK SCREENSHOT.JPG
    165.2 KB · Views: 4

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 16, 2009
Messages
42
Due to the urgency of this request, I've cross-posted it at the following link:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,967
Members
425,653
Latest member
UNSING

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
Top