Good morning everyone,
I am trying to reduce my manual data entry. Who isn't? lol
Is there anyway I use pivot tables in my spreadsheet "Example Master Inventory Sheet" to automatically report that data like in these two separate spreadsheets: "Inventory Report by lot #" & "Monthly Inventory Report - loading and unloading"?
I'm so new to tables and pivot tables, but I can already see the advantage of such features!
Hello ZM003 and Welcome to Excel Forum.
In order to produce a pivot table I believe that you will need to convert the data in a "records" format.
I used Get & Transform to convert most of the data on the ISOLEUCINE 20 KG - INDONESIA sheet into records (green and white table) using the following Power Query Advanced Editor code:I imagine that converting the data on the Inventory by Lot# Report sheet into 'Records' format would be a manual process.
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I realized that the values in the END INV, LBS columns should be populated using formulas.
END INV: =[@[BEG INV]]-[@Used]
LBS: =[@[END INV]]*44.092
Also after the initial balances are placed in the BEG INV column on 1/3/2022 the rest should be populated by a formula.
Starting with cell B24 the formula is: =INDEX(E$17:E23,AGGREGATE(14,6,(ROW(E$17:E23)-ROW(E$16))/(B$17:B23=B24),1))
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks