I have one sheet which is full of Purchase Order Data. From that I constucted a Pivot Table that just reports the Budget Line, PO No & Spend Year.
I used the Offset Formula as the table is continously growing.
In another sheet I have all the Budjet Data and so I have added more columns of which one is the current Purchase Order Spend against the Budget. However I had to construct another Column to show the value of the Purchase Order where the Supplier is BLANKET, the reason for BLANKET is to show a spend against a budget where a supplier has not yet been chosen.
I now have 2 columns, one will have the total spend from the pivot table where BLANKET is not met, and the other where it is met. I then have another Column for Invoiced amount and finally an Accrual Column.
That in a nut shell is it. Getting the basic data is not a problem, it is a problem when I needed to meet 2 consitions in a pivot table, and of course deal with the issues of data not being found or N/A being returned.
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.