Problem with summing multiple products & quantities. Not sure how to approach.

Hi guys,

I am relatively new to intermediate excel functionality so bear with me. I've attached a sample proposal I am working on. I'm not sure if Macros are the solution here but my fundamental problem is I do not know how to automate a search of product names, sum their quantities and then have them automatically populate in another worksheet.

Please take a look at the attached workbook.

What I’m trying to do is find a way to automate the “estimating” worksheet based on the “proposed lighting system” in “ES Calculator”. You’ll see a number of different products and the corresponding proposed quantities. The objective is to take these quantities, sum them up and input them into the “estimating” worksheet where a price will be calculated. You’ll see the same product entered in multiple spots on the “ES Calculator” tab; this is because different rooms will need the same fixtures but it’s broken down on a room-by-room basis. I think setting up a macro to search for product names and their quantities, summing them up and adding them to the “estimating” worksheet would solve the problem (I have no idea how to approach this). The idea here is full automation.

FYI – the “ES Calculator” worksheet is copy and pasted from a different workbook that is provided by a third party. We use their workbook to calculate the numbers and just copy and paste the whole worksheet into our workbook. It has to be this way for a variety of reasons.

Also, I’m looking at creating and automating a “scope of work” worksheet (no work complete on this yet). This is where I will have something similar to the “ES Calculator” - how it has a column for existing lighting and a column for proposed lighting systems. Essentially it will show the client what the current system is and what its suggested replacement is. It will be copy and pasted from the workbook in to the actual proposal the client will see. I’m wondering if macros are the solution here as well. I’m not familiar with the range of capabilities that macros have, but I was thinking if excel can recognize which products on the “ES Calculator” (or in the “estimating” tab) have quantities entered and then add those to the “scope of work”. Does that make sense?

Let me know if I can clarify and points.