INTERACTIVE ORDER FORM
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INTERACTIVE ORDER FORM
Here is a new version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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:
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:
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INTERACTIVE ORDER FORM
How could I have known that you added a column to the ORG LIST sheet? My crystal ball is away for repairs.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INTERACTIVE ORDER FORM
New version once again.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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!
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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
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
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INTERACTIVE ORDER FORM
Aargh - I'm sorry, I made a mistake copying some code.
Here it is again, renamed.
Here it is again, renamed.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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.
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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:
Really appreciate all that you've done to help us!
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)
- 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.
- 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))
- 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.
- 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.
Really appreciate all that you've done to help us!
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INTERACTIVE ORDER FORM
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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
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
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INTERACTIVE ORDER FORM
I have edited the workbook.
You should do the following for each organization:
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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 ** 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. 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"
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 ** 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. 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"
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INTERACTIVE ORDER FORM
Here is a new version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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
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
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
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. 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) So step #2 above is incorrect. Other than that every single function worked perfectly.
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. 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) So step #2 above is incorrect. Other than that every single function worked perfectly.
You do not have the required permissions to view the files attached to this post.
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
Forgot to attach the workbook for you to see it in action.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INTERACTIVE ORDER FORM
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.
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 20 Mar 2018, 13:40
Re: INTERACTIVE ORDER FORM
Okay I'll retry that now. I didn't do that step first. I simply clicked NEW ORDER.