INTERACTIVE ORDER FORM

Post by **HansV** » 18 Sep 2021, 06:49

Here is a new version.

PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsm

COGICPENNY · Post by **COGICPENNY** » 19 Sep 2021, 01:14

The new form is 99.99% ready. The good news first: The "NEW ORDER" worked PERFECTLY!!! The balance carried over just right.

Now the error, since I didn't have an email address in a field called "EMAIL" (I typed one in to use for the test "jacob@powerchangeslives.com) on the "ORG LIST" (added the field on this tab for "email") when the macro ran to "PLACE ORDER" it gave the following error code:

ORG EMAIL NAME 09182021.JPG

EMAIL ERROR 09182021.JPG

Post by **HansV** » 19 Sep 2021, 06:53

How could I have known that you added a column to the ORG LIST sheet? My crystal ball is away for repairs.

Post by **HansV** » 19 Sep 2021, 07:48

New version once again.

PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsm

COGICPENNY · Post by **COGICPENNY** » 19 Sep 2021, 11:27

Lol you absolutely wouldn't have known. I was saying that during the test from the previous version before what you're providing today, it failed because it needed an email address for the organization. So I created one, entered a data value for it and sent a new post updating you. I will test your latest version now. Appreciate you so much!

COGICPENNY · Post by **COGICPENNY** » 19 Sep 2021, 12:43

Good Morning,

Tested the file. Same error code "Organization email is not recognized!". So the last change requested to send the restaurant a copy of the email is not working. Not sure if the organization's email is working either because the test fails before that point (or maybe after don't know because it won't generate the Outlook email prompt as it did before).

When you send it back can you rename the file with the current date so I can easily track the edits? I've been looking at the download dates to make sure it's the latest version.

Thanks, hopefully I can send this out today so that the programs can begin feeding people on Monday.

Penny

Post by **HansV** » 19 Sep 2021, 13:39

Aargh - I'm sorry, I made a mistake copying some code.
Here it is again, renamed.

PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09192021.xlsm

COGICPENNY · Post by **COGICPENNY** » 19 Sep 2021, 14:22

I'm at church now it's 10:21 am my time, but I'll test as soon as I can get home later this afternoon. Appreciate you.

COGICPENNY · Post by **COGICPENNY** » 20 Sep 2021, 01:17

Good Evening Hans,

Unfortunately, I spoke too soon without fully testing the form from my laptop and found that it once again failed. I still consider us steadily progressing forward in the right direction. Please note the following:

ORDER FORM:

F10 (CURRENT): =IF(F3="","","I-"&B17&"-"&TEXT(F3,"mmddyyyy")&"-"&L10)
F10 (CHANGE TO): =IF(F3="","","I-"&B17&"-"&TEXT(F11,"mmddyyyy")&"-"&L10)

ORDER FORM 2:

F25 (QTY): should be blank when clicking “NEW ORDER”, no data should appear until user enters it

F10 (CURRENT): =IF(F3="","","I-"&B17&"-"&TEXT(F3,"mmddyyyy")&"-"&L10)
F10 (CHANGE TO): =IF(F3="","","I-"&B17&"-"&TEXT(F11,"mmddyyyy")&"-"&L10)

F7, F8: Drop down options unavailable.
Drop Down Menu.png

ROWS 14-17: ORDER FORM 2 defaults to BECK’S BIRTHING CENTER despite testing the original ORDER FORM with a different organization.

E17: AWARD AMOUNT should always remain the starting balance found on the original ORDER FORM (E17) or use the values found in tab ORG LIST (COL H)

H17: AWARD BALANCE is (E17-I31 value from ORDER FORM (I31))

ORDER FORM 3 (AND ALL NEW ORDERS):

F25 (QTY): should be blank when clicking “NEW ORDER”, no data should appear until user enters it

F10 (CURRENT): =IF(F3="","","I-"&B17&"-"&TEXT(F3,"mmddyyyy")&"-"&L10)
F10 (CHANGE TO): =IF(F3="","","I-"&B17&"-"&TEXT(F11,"mmddyyyy")&"-"&L10)

F7, F8: Drop down options unavailable.
Drop Down Menu.png

ROWS 14-17: new ORDER FORM defaults to BECK’S BIRTHING CENTER despite testing the original ORDER FORM with a different organization.

E17: AWARD AMOUNT should always remain the starting balance found on the original ORDER FORM (E17) or use the values found in tab ORG LIST (COL H)

H17: AWARD BALANCE is (E17-I31 value from ORDER FORM (I31)) minus I31 value from ORDER FORM 2. This will reoccur each time a new order is placed and to obtain a “running down” of the AWARD BALANCE until it is depleted.

I am attaching a new Excel sheet to this email as we made a couple of cosmetic, NON-FORMULA changes (e.g., PICK-UP/DELIVERY DATE:, PICK-UP/DELIVERY TIME:, PICK-UP/DELIVERY TYPE: )

Really appreciate all that you've done to help us!

Post by **HansV** » 20 Sep 2021, 10:05

If the organization can change within the same workbook from order to order, the balance will become meaningless. I thought you wanted to give each organization its own workbook where the organization won't change.

COGICPENNY · Post by **COGICPENNY** » 20 Sep 2021, 10:56

Good morning,

Please let me clarify. The organization DOES NOT CHANGE within the workbook. We were stating that if we change the organization on the ORDER FORM (first time placing an order) so that EACH ORGANIZATION has their own WORKBOOK (separate Excel File document), the organization "BIRTHING CENTER" is hard coded in the file and appears when you click NEW ORDER when they weren't the original organization on the ORDER FORM. It's like having my American Express statement for the month of August one month (ORDER FORM), and the next month instead of it saying AMEX again it says '"VISA" (ORDER FORM 2) yet it is the SAME ACCOUNT and should say "AMEX" (info carrying forward from ORDER FORM).

We will be creating and distributing this order form to 29 SEPARATE, INDIVIDUAL ORGANIZATIONS who will maintain their own file, only THEIR NAME should appear on each and every sheet within the workbook. However, as this form currently stands, it has hard coded to revert back to our nonexistent, SAMPLE data (BIRTHING CENTER) and not the single new organization we choose that will be the only one on ALL WORKSHEETS within the workbook.

Pray this all made sense in my explanation.

Appreciate you so much. Penny

Post by **HansV** » 20 Sep 2021, 11:45

I have edited the workbook.
You should do the following for each organization:

Open the workbook.
There is no ORDER FORM sheet, and the ORDER FORM TEMPLATE sheet is blank.
In the ORDER FROM TEMPLATE sheet, select the organization's phone number.
This will populate the organization data.
Click in the cell where the ORDER DATE should be entered, or whichever cell you'd like the user to start entering data in.
Click NEW ORDER. This will generate the first order form, with the organization's data pre-filled.
Press F12 to save the workbook with a new name, preferably including the organization in the file name.
Close the newly saved workbook.

PROJECT FEED ORDERING SYSTEM - REVISED (TEST2) 09192021.xlsm

COGICPENNY · Post by **COGICPENNY** » 20 Sep 2021, 12:18

Good Morning,

First, this revision was definitely a HUGE STEP FORWARD! There are just some minor glitches in which the test failed:

ORDER FORM 1:
H17 (MEAL BALANCE): Never changed despite me ordering 1,000 meals from my new test company "POWER CHANGES LIVES" that has 25,000 meals to consume. It should've reflected 24,000 meals after ordering 1,000 meals in ROW 25, but it did not. The user would be forced to manually calculate what their meal balance is during this transaction. They should be able to see it on this sheet so that in the event they are "running low" on their available balance they can make the adjustments in their quantity(ies).
** The remainder of the form PASSED and worked perfectly **

BALANCE CONSUMED (ORDER FORM).JPG

ORDER FORM 2:
H17 (MEAL BALANCE): Wrong balance was carried forward. This happened because it used the TOTAL COST (H25) from ORDER FORM 1, instead of using the QTY (F25) or even (I31) from ORDER FORM 1. When this happened, it incorrectly shows POWER CHANGES LIVES INC has an AWARD BALANCE (H17) of $15,000 when based on their previous consumption on ORDER FORM 1 they should have an AWARD BALANCE of $24,000 because they only consumed 1,000 meals.

ORDER FORM 2 (AWARD BALANCE).JPG

Also, by FYI, the AWARD BALANCE (H17) is a number not a currency and the AWARD MEALS (E17) is also a number not a currency.

Would it be possible to rename the file on today's date so that I can keep track of which version we are working off of? I have another tester besides myself looking at the document.

Once these are fixed, the sheet is beyond perfect! So extremely grateful, and how patient you are with us. You should be named "St. Hans"

Post by **HansV** » 20 Sep 2021, 14:18

Here is a new version.

PROJECT FEED ORDERING SYSTEM - TEST 09202021.xlsm

COGICPENNY · Post by **COGICPENNY** » 21 Sep 2021, 01:51

Good Evening Hans,

Whoop whoop, just wanted to say that you did a SUPER job!!! This is exactly what we imagined and you are so talented and amazing to bring this to life

! We are very appreciative for all your hard work and can't thank you enough: thank you, Thank You, THANK YOU

!! The form is perfect and we are sooooooo grateful your your help and patience!!

Thanks again,

Penny

Post by **HansV** » 21 Sep 2021, 10:07

Thanks for your kind words!

COGICPENNY · Post by **COGICPENNY** » 28 Sep 2021, 19:47

Hans,

My apologies. I've been out of state traveling for training to help the homeless in California. The time zone difference kicked my butt and I'm EXHAUSTED!!!

So your form worked ALMOST perfectly, I didn't catch something. Here's what happened (see attachments)

1. I followed your instructions on the TEMPLATE tab and clicked NEW ORDER. This created tab "-1" (SUCCESS)
2. The quantity I purchased in my example was for 150 "hamburgers and fries". Cell H17 (AWARD BALANCE) showed a "-150" (FAIL) when it should've shown 138,850.

ORDER FORM 1 (AWARD BALANCE) 09282021.JPG

3. I clicked NEW ORDER again. This created a tab "-2" (SUCCESS).
4. The quantity I purchased this time was 1 pan of soup (serving size 10) and it calculated Cell 17 (AWARD BALANCE) as 138,840 (which is the previous tab balance of 138,850 minus the 10 meals. (SUCCESS)

ORDER FORM 2 (AWARD BALANCE) 09282021.JPG

So step #2 above is incorrect. Other than that every single function worked perfectly.

COGICPENNY · Post by **COGICPENNY** » 28 Sep 2021, 20:05

Forgot to attach the workbook for you to see it in action.

PROJECT FEED ORDERING SYSTEM - TEST 09282021.xlsm

Post by **HansV** » 28 Sep 2021, 20:16

Please see this earlier post.
You must fill in the organization's phone number on the ORDER FORM TEMPLATE sheet. Only then create the first ORDER FORM, and save the workbook with a new name.

COGICPENNY · Post by **COGICPENNY** » 28 Sep 2021, 20:21

Okay I'll retry that now. I didn't do that step first. I simply clicked NEW ORDER.

Eileen's Lounge

INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM

Re: INTERACTIVE ORDER FORM