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.

Originally Posted by Glenn Kennedy

The problems here are on your Estimating sheet. The entries in column A do not correspond EXACTLY and UNAMBIGUOUSLY with the entries in Calculator copy.

In Calculator Copy there is a 2L-F32T8 25W (cell K8) that looks pretty precise : code and wattage; but on Estmating you have: NEW INDUSTRIAL: 2L-F32T8 25W at one price and RE-LAMP: 2L-F32T8 25W at another price.

1. How can I tell on sheet Calculator if it's a NEW or a RE-LAMP that's neeed?

2. Once that's sorted; is it possible to split NEW INDUSTRIAL: 2L-F32T8 25W into two columns: NEW Industrial and 2L-F32T8 25, or some other way of getting a EXACT and UNAMBIGUOUS match?

Other examples include LED-WALL-50W on one sheet; NEW: LED-WALLPACK-50W on the other. Your sheet must be altered to get these to match. That's easy; my big problem is the difference between the prices of new versus re-lamp.

Hi Glenn, thank you very much for your response. Sorry it's taken me a few days to get back.

You're absolutely right, there is a glaring issue with the NEW and RE-LAMP categories and their respective price differences. I've been trying to figure out a clever way to differentiate the two categories so my ESTIMATING worksheet can auto-populate appropriately. What I've come up with so far is that the quantities of a particular product from the ES CALCULATOR tab (i.e. 2L-F32T8 25W) can by default populate in to the RE-LAMP-2L-F32T8 25W cell of the ESTIMATING worksheet, but somehow tell excel that if there is anything written in the "comments' cell at the far right of the ES CALCULATOR on any particular product, then put the quantities in to the NEW INDUSTRIAL cell.

So I can write "new" in the comments section on the ES CALCULATOR sheet in the 2L-F32T8 25W row and it will populate in the ESTIMATING workbook as NEW INDUSTRIAL. If I don't type anything in the comments, it will default to RE-LAMP. It's not a perfect solution because maybe a particular room that requires, say 70 lights, may need some NEW and some RE-LAMPS, but it's the best solution I've thought of as of yet.

Do you have any other thoughts on this Glenn?

Thanks.

Originally Posted by Glenn Kennedy

Apols. i've been away for a few days with only a very poor www connction. OK. Give it a go. Can you mock it up again - with a few commented cells as suggested. However, I DO need an answer to Q2 above (the wording of which I have modified slightly) and it would also be good if you coud address the last point as well before re-posting.

Thanks Glenn.
OK, I'll give it a try. To answer Q2, yes absolutely.

I've solved this by making new column for NEW or RELAMP and using the SUMIF function. Thanks again.