match, sum if, monthly report

cgreen

Active Member
Joined
Aug 14, 2002
Messages
291
Hi All ... Hoping you can help

example a)
ordering database (live data)
Purchase
Order

Jan ABC 15
Jan GHI 9
Jan DEF 5
Feb ABC 28
Feb DEF 5
Mar ABC 35
Mar GHI 9
Mar GHI 15
May GHI 9
May ABC 8

example b)
managements data (authorized amounts)
Purchase
Order Jan Feb Mar Apr May
ABC 10 15 10 25 30
DEF 5 8 5 20 3
GHI 10 10 10 25 10

example c)
monthly report
Column A Column G Column I Column K
Mar
Purchase Order Mnmt Data Ordering Database difference
ABC 25 35 -25
DEF 20 0 20
GHI 25 24 1

When I run an update on the monthly report, I want it to look at the month (jan, feb, ect.) on the ‘ordering database’ and ‘management data’ then update my report for the month I am looking at to see if the orders are being placed as ordered by management.

The month of the report will always be located in “A1” on the monthly report and the format for the month will be the same on all three reports. So on the ‘monthly report’: column G will look at the ‘managements data’ for the month, then the purchase order and give me the number allowed for that month. ABC=25, DEF=20 and GHI=25. column I will look at the ‘ordering database’ for the month, then the purchase order and sum the total purchase orders to the month ordered ABC=35 and GHI=9+15. Then column K will have (column G – column I).

How do I write the formula in column 'G' and 'I' on the monthly report?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,147,690
Messages
5,742,638
Members
423,746
Latest member
Joaogomes

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