Forum Discussion
Calculating Two Areas in Excel
Hello,
I have a spreadsheet that is broken down into two parts. The first section of rows is an order like form that users can select products and put down the quantity. The second section is where those products will be delivered. If the total order of products in the order section is 24 (column D) then the delivery section (column D) must add up to 24.
What formula could I use to calculate one column (column D) in the order section to match against the total in the delivery section (column D) ? If the two numbers do not match an error would display something like, "Order section does match delivery section".
The order section is rows 11 - 65 and the delivery section is rows 72 - 120.
Thank you in advance.
Steph
If you don't see the Developer tab of the ribbon, select File > Options > Customize Ribbon, tick the check box Developer in the list of Main Tabs on the right, and click OK.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the following code into the worksheet module:
Sub CheckIt() If Application.Sum(Range("D11:D65")) = Application.Sum(Range("D72:D120")) Then MsgBox "Review Completed", vbInformation Else MsgBox "Order quantity does not match delivery quantity, please review!", vbExclamation End If End SubPress Alt+F11 to switch back to Excel.
On the Developer tab of the ribbon, in the Controls group, select Insert > Button (Form Control).
Then click on the sheet where you want the button.
Excel will prompt you to select a macro; choose CheckIt, then click OK.
Right-click the button and select Edit Text from the context menu.
Change the caption to Review Order.
Click outside the button when done.
(If you need to move or otherwise modify it later on, right-click on the button)
Save the workbook as a macro-enabled workbook (.xlsm), and make sure that you allow macros when you open it.
4 Replies
For example in D122:
=IF(SUM(D11:D65)<>SUM(D72:D120),"Order section does not match delivery section","")
Make the cell with the formula stand out by using a large font size and a striking font color, for example red.
Works great!!! One additional question. I'd like to create a button at the top of the form that says "Review Order". Once they populate the form the user will click the "Review Order" button and it will then review the order and delivery section column and either flag it, "Order quantity does not match delivery quantity, please review" or if all is good, display "Review Completed". I'd like the message to be something like a pop-up note/sticky note look.
Can that be done?
If you don't see the Developer tab of the ribbon, select File > Options > Customize Ribbon, tick the check box Developer in the list of Main Tabs on the right, and click OK.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the following code into the worksheet module:
Sub CheckIt() If Application.Sum(Range("D11:D65")) = Application.Sum(Range("D72:D120")) Then MsgBox "Review Completed", vbInformation Else MsgBox "Order quantity does not match delivery quantity, please review!", vbExclamation End If End SubPress Alt+F11 to switch back to Excel.
On the Developer tab of the ribbon, in the Controls group, select Insert > Button (Form Control).
Then click on the sheet where you want the button.
Excel will prompt you to select a macro; choose CheckIt, then click OK.
Right-click the button and select Edit Text from the context menu.
Change the caption to Review Order.
Click outside the button when done.
(If you need to move or otherwise modify it later on, right-click on the button)
Save the workbook as a macro-enabled workbook (.xlsm), and make sure that you allow macros when you open it.
