Best table format/layout to use.

**mgnsllvn** · 01-19-2022, 11:14 AM

Hello everyone.

I am a new user with a relatively moderate knowledge of excel. I use it in on a day to day basis and have taken an advance excel course, but, since I don't regularly use advance techniques, I have lost some of that knowledge.
I have a specific problem what I am struggling with. I am trying to determine what is the best way to go about displaying my data, as well as making it easier to manipulate if needed.

I will try to explain this to the best of my ability.

I make parts for the Kia Seltos. Of that Kia Seltos, there are three potential variations that would require some of the same, and some different components. I need to list inventory levels of all the components, which will help me determine how much of a variation I could make.
Please see the attachment for more information. Hopefully this makes sense. I am wondering if the route that I am taking now, is the most efficient and viewer friendly.

I have tried making levels, but with the totals for each variation, it isn't quite working. Each total for the variations is not going to account for any other parts. Meaning we don't need to take other variations into consideration when coming up with the total/inventory levels.

I am familiar with pivot tables, so if I could figure out how to layout the source and pivot tables, I have no objection to trying that.

Thank you in advance for any help

**dflak** · 01-19-2022, 10:14 PM

First of all, you want to round up your data in a normalized format. I did this partially on the Model Sheet.

The first table (black) is Table_BOM (Bill of Materials). It shows how many of each part goes into each assembly. In your model it appears to be one of each. I've modified the data a bit so I could test it. So for the Subaru Outback, Assembly FW04993, you need 5 R216-0053 and one R685-0192. You also use R216-0053 in assembly FW04934, but you only need one of them there.

There is a blue table on this sheet too. This is the inventory (Table_Inventory). I use a VLOOKUP to pull those values into Table_BOM.

I then made a pivot table and I made a calculated field with the formula =ROUNDDOWN(Inventory/Number Required,0). So if it takes 5 of something to make an assembly and you have 19 in inventory you can make 4 of them.

I put a couple of slicers on the table because that makes them "cascade." That is when you select a Make in the Make slicer, only those assemblies associated with that make vehicle will be displayed.

About the only slight of hand is the assignment of a named dynamic range (Supports_List) to overlap the Supports column of the pivot table. I could have done a MIN (M:M) as well,

The answer is in cell J10 ="Minimum make: " & MIN(Supports_list)

**mgnsllvn** · 01-20-2022, 11:49 AM

Thank you so much! This helped me immensely! I have continued on with your suggestion and am positive that this will work for my needs.

I do still have one point that I am struggling with in the pivot table.

I would like every assembly, or as I have it labeled, NAGS Number, to have its own subtotal line showing the maximum amount that can be made (based off of the component with the least inventory). I cant quite figure out how to get each NAGS to have it's own "QTY can be made" line.
However, This number is generated in the same row as the NAGS number in the pivot table. So, I could settle for removing the "QTY can be made" row at the bottom all together. But I cant figure out how to get rid of that with out removing the whole column.

**dflak** · 01-21-2022, 12:35 PM

Here's a slight tweak to the pivot table.

Right click on the assembly and select field settings. Go to Layout and Print and check OFF Display totals at the top of each group.