jimithing007
New Member
- Joined
- Jul 12, 2017
- Messages
- 16
- Office Version
- 365
Hello,
Lower level Excel user here, hoping to get some guidance.
My company has to have a spreadsheet to track inventory usage for some components for a daily report. We need to track daily exactly how many of each item we receive into our warehouse and how many we consume to build orders. The final tab will be our "on-hand" quantity - the difference between received and consumed.
I have four tabs on this mock spreadsheet (screenshots linked below) -- "Receiving Report," "Consumption Report," "Consumption - TYPE Z items," and "Currently On Hand." The formula needs to display the result on the “Currently On Hand” tab under the corresponding date and by the corresponding “Supplier Part #” (Column A).
So we need a formula that will look at the “Receiving Report” tab, then take the “Qty Received” in Column D that corresponds with “Supplier Part #” in Column C, by the “Received Date” in Column E. Then it needs to look at the “Consumption Report” tab to determine the total number of each item consumed in a given day. Each item could be listed more than one time in a day. Each item could also be listed more than once a day on the “Receiving Report” too, as we have to keep track of each PO# for which they arrive. So the formula will need to be able to accommodate that.
The difference between the Receiving Report and the Consumption Report should display on the “Currently on Hand” tab by the corresponding date.
For the five items labeled “TYPE Z” at the end – those quantities received will still be reported in the Receiving Report tab, but the consumption quantities have to be tracked separately, hence the “Shipments – TYPE Z items” tab. Otherwise, this is handled the same way, and the result should display the same on the “Currently On Hand” tab.
Any help is appreciated. Please let me know if any clarification is needed. I'm using Excel 2016. Here's some screenshots of each of the four tabs on the report, if this helps:
https://imgur.com/a/dGaGO
Lower level Excel user here, hoping to get some guidance.
My company has to have a spreadsheet to track inventory usage for some components for a daily report. We need to track daily exactly how many of each item we receive into our warehouse and how many we consume to build orders. The final tab will be our "on-hand" quantity - the difference between received and consumed.
I have four tabs on this mock spreadsheet (screenshots linked below) -- "Receiving Report," "Consumption Report," "Consumption - TYPE Z items," and "Currently On Hand." The formula needs to display the result on the “Currently On Hand” tab under the corresponding date and by the corresponding “Supplier Part #” (Column A).
So we need a formula that will look at the “Receiving Report” tab, then take the “Qty Received” in Column D that corresponds with “Supplier Part #” in Column C, by the “Received Date” in Column E. Then it needs to look at the “Consumption Report” tab to determine the total number of each item consumed in a given day. Each item could be listed more than one time in a day. Each item could also be listed more than once a day on the “Receiving Report” too, as we have to keep track of each PO# for which they arrive. So the formula will need to be able to accommodate that.
The difference between the Receiving Report and the Consumption Report should display on the “Currently on Hand” tab by the corresponding date.
For the five items labeled “TYPE Z” at the end – those quantities received will still be reported in the Receiving Report tab, but the consumption quantities have to be tracked separately, hence the “Shipments – TYPE Z items” tab. Otherwise, this is handled the same way, and the result should display the same on the “Currently On Hand” tab.
Any help is appreciated. Please let me know if any clarification is needed. I'm using Excel 2016. Here's some screenshots of each of the four tabs on the report, if this helps:
https://imgur.com/a/dGaGO
