Formulas or VBA better for this application?

[drewship]

Good morning all. I need to sum quantities of parts on sheet "1_ImportedData" column "F" (QTY) that match a specific part number or a named range and paste the matching "Customer Name" from "ZZ_Maintenance" column "C" and sum for that part and customer. The "Customer Name" (hyperlinked to the matching Customer sheet) would go in the matching equipment column on 3_Equipment" sheet and the sum would go in the "Onhand QTY" column for that part. There is an authorized quantity which is on the "ZZ_Maintenance sheet" for each customer and part that needs to be copied to the 3_Equipment" sheet as well in the "Authorized QTY" columns. 3_Equipment in the attached workbook shows the desired result. Since I am using some Named Ranges as well as single part numbers, I have no idea which method (VBA or Formulas) would be best to accomplish this (or even if one or the other can do this on its own) so if anyone has some example code or formulas I can try, it would be greatly appreciated.

Thanks in advance,
Andrew

[drewship]

Tried

=SUMIFS('1_ImportedData'!$F2:$F30,'1_ImportedData'!$A2:$A30,Computers,'1_ImportedData'!$D2:$D30,ZZ_Maintenance!$B3:$B7)

in the "Onhand QTY" columns and it seems to work as long as I use a fixed value like "234" where "Computers" is in the formula, but the named range 'Computers' does not sum the data. This only sums the qty of each part for the customer so the customer and authorized quantity still need to be added to their respective columns somehow.

EDIT: Just realized the formula only matched the first part and name in the range so it would have to be modified to cycle through the entire range...don't think a formula can do this...

[newdoverman]

It seems to me that you have made this far too complicated. All the data could be on one worksheet and with the use of Auto Filter, extract all the reports that you have on each individual's worksheet. If you convert the data on 1_ImportedData into a table with a Total row, you can get various counts as you filter the records.

The use of a Pivot table that uses the data on 1_ImportedData will summarize all your data very easily.

It appears that the Green, Yellow and Red are just counts of the various inventory items which are already incorporated into the 1_ImportedData worksheet.

[drewship]

Thanks for the reply and example newdoverman! I am not familiar with Pivot tables, but looking at your example I am not sure that will work for this workbook. In the production workbook, there are over 550 customers each of which could have any combination of 24 different parts (or a summed count for a group of similar parts, like different models of computers all being a single summed value representing all computers). So I thought it would be more manageable to have all the customers listed down with the quantity of each item listed across. Additionally, since I need the required quantities as well as the onhand quantities, it seemed easier to list them next to each other. I have updated the 3_Equipment sheet with a better example.

[newdoverman]

Are you telling me that you are going to have 550 tabs for the customers as well as a listing for those customers on the 3_Equipment and 1_ImportData worksheets? If that is the case, that is going to be extremely difficult to maintain.

The method that I showed you will allow for many different calcuations very easily without having to have all those worksheets.

[drewship]

Yes, over 550 tabs. I copy the imported data from a report, paste it to the 1_ImportedData sheet, and press the Process button on the 2_Summary sheet. The VBA adds new sheets with data if there are new customers and clears current sheets and pastes imported data into them, and various formulas and conditional formatting counts and colors as required. The color coding is the critical thing so management can see and quickly go to the desired customers for additional review of their parts status. I will research the pivot table and see if it can provide the required functionality. I really appreciate the example you provided since it relates to my example and will help me understand while I research it!

Andrew

[TMS]

Totally agree with newdoverman. I can see this workbook being huge, slow, and unmanageable and would expect it to crash and burn.

I'd suggest you put the Structured Table a few rows down and use Data Validation to select customers and parts. It would be easy enough (I think, off the top of my head) to produce a unique DV List of customers and, when a customer has been selected, a unique DV list of parts for that customer.

Regards, TMS

[drewship]

TMS and newdoverman, I am all for making it as efficient as possible!!! The file is currently 4.23 MB and takes about 15 minutes to process, but once it is done, we can use the 2_Summary sheet to quickly see which customers are red and yellow and with a click on the customer link, we are looking at the customer detail sheet with the individual equipment and their quantity status colors. Each customer has unique equipment requirements which adds to the complexity. Other than the initial processing time (which would probably happen once a week), everything else works perfectly. We want to add the equipment reporting as a way to quickly identify groups of customers and their equipment. For example, we might want to only see the Desks for all customers in USA states. On the 3_Equipment sheet, we can filter on the Desk column for example, for just customers in the USA to see the quantity of Desks in each state to verify the Onhand quantity against the Authorized quantity. I hope I explained that so it makes sense.

[newdoverman]

It is obvious from your msg that there are more data types than in your example. Even so, this "model" even though far from perfect can produce just about any report that would be required using only 3 worksheets.
You can filter the data on the ImportedData worksheet to get many different combinations of data.
On the Customer data worksheet, new customers are added with their address and the name will be added automatically to the DV list for the names on the ImportedData worksheet. Quantities of the various products are also calculated for each customer.
On the Equipment worksheet the various pieces of equipment can be broken down by customer. (This can also be done on the first worksheet which includes addresses). It does have a small very simple inventory area that the other worksheets don't.

[drewship]

Thanks newdoverman, that is pretty impressive and much simpler that what I have at the moment. I will try to adapt it to my production data and I did some research yesterday and found that it is possible to color code data in the Pivot Table so that will help. Thanks so much for your help!

[TMS]

Thanks for the rep

[newdoverman]

Thank you for the feedback and rep.
Good luck with your project.