Need to summarize data from multiple sheets

I have a price sheet that we created. A copy of each sheet will be duplicated for each sold product. It takes the cost of raw materials, labor, etc and calculates a sales price for me based on the profit margin I set up. I used a VLOOKUP formula for the raw material because the cost of these could change and I want to be able to change it in one spot and it to update all the sheets. All this is completed, but I also want one sheet that is a summary page for all products and their prices. Below is a screen shot of the individual price sheet

Price Sheet 1.PNG

For the summary I know I can just have each cell equal the cell from the other sheet like the image below, but I will have 50 plus products so I don't want to have to do that over and over. Is there a simpler way?

Price Sheet 1.PNG

If a simpler solution is possible I would also like a way for excel to keep the previous price(if there was one), but I don't know if that will be possible. This would make it very simple If I changed the cost of one raw, but it affected 10 products, I could go to the summary and easily see and print out a updated price sheet due to that cost change.

Price Sheet 2.PNG


Any help would be greatly appreciated.

I figured out I can make a pivot table for multiple data sources, but I'm not sure if it's that much faster. I still have to add the fields from each sheet to the pivot table wizard. I tried this and it worked, but I now have the issue "what if i add a new product"? I changed the pivot table source to add the new field and refreshed the pivot table but it did not add it. So I am at a loss on how to do this.

I added the test file. There is a Summary tab that I would like the date to roll up to. You will see the simple formula I used and the pivot table. I just am curious if there is a simpler way to do this. Especially when I eventually have 50+ tabs(sources) I will need data from, not to mention adding new sheets.

It would also be nice to have a way for a cell to automatically contain the previous pricing automatically.

In "Summary"

in B3

=IF(A3="","",INDIRECT("'" & A3 & "'!$D$9"))

Copy down

I don't know you can get the "Last Price" because it will be lost as soon as you change "Price" i.e "Last Price" is "Price" prior to change.

Using VBA might be a possible solution.

Thank you. That worked for the pricing. I'm not familiar with the INDIRECT formula so I'm not sure what it's doing, but I'm just happy it works. Is there a similar formula I can do for the product name from each tab.

Sorry but I don't follow: the formula I provided uses the "product" name (equivalent to the tab name) in Column A.

If you want the name from D7 in each tab then ..

=IF(A3="","",INDIRECT("'" & A3 & "'!$D$7"))

BUT this assumes there is an additional column e.g Product Name, ,in "Customer Price Summary" ; the current "Product" column is the tab name.

What I am asking is probably not possible. In addition to the 1st formula you gave me I was also wanting the product name from D7 of each product tab to be on the summary page. Right now I just have a formula that equals that cell name(see pic), but if I have 50 products I would have to do that over and over again for each cell. Is there a more simple way, maybe a formula that I can make one but copy and paste down?

new formula.PNG

With formula your only option is per my last post:

column A is a list of your tabs then column B will extract the product name from the sheet in A

the price you pay for having a sheet per product!!! (rather than one sheet for all products)

With that formula it would go the opposite way that I need though. With this formula I would need all the product names listed in column A of that summary page and the formula you gave me would go into each product tab to generate the name. I wanted it the other way around.

Unfortunately we create the product tab first. When we need a new product we just copy the tab, change the product name , select new raw materials, and that will give us a price for that product.

If I follow you correctly, then the only way to get the product name from each product tab is to do it one by one or just manually type it in, right?

we create the product tab first

You just add the new tab name in column A: no need to change the formula in B or C

You appear not have looked at the last file I posted ????

I did miss that. Sorry. I see what you did there. Thank you very much. It's six in one/half dozen the other I think.

We just started this spread sheet and we are going to add many tabs. Once we are done there will be 50-100 tabs of products. It sounds like I either just manually type in the product name and use the formulas for pricing, or type in the tab name and use a formula for product name and pricing. Either way I need to type in 50-100 names, No easy way to create one formula that I can copy and paste that grabs all the product name.

Like you said. That's the downfall for using so many different tabs, I was just hoping there may be something I had not thought of.

Thank you again. Sorry if I made this confusing.

The only other option is to use a macro to list the tab names which you can copy into A.

However I don't consider having to add a new tab too onerous: you are going to have add tab names to sheets anyway.

Please Login or Register  to view this content.

Macro to list "Product" tabs: if Tab names follow a convention e.g always "Product XX" then macro could be amended slightly,

Wow. Thank you for that!

I don't have experience with macros.

I really only need the product name and price on the summary tab, but It looks like I need the "Product _Tab" column to make it work. I can always hide that column. You would not happen to know an easy macro for previous price would you?


product tab.PNG

For "previous" price we need to know which parameters are included in any price calculation because a change to any one will result in the current price being changed.

We therefore have to "intercept" the new price before it changes so we can record the current price as "Previous".

So "Primary Raw Materials" are candidates as are Secondary Costs. In addition, changes to the data in "Raws" also affects the price so it is a fairly complex set of parameters we have "monitor".

I will need to think if, and how, this can done but it will require VBA (macros): it cannot be done with formulae.

Yeah. It sounds a bit complex. Don't go out of your way to think of a solution. You have already helped me so much and I really appreciate it.

See attached:

Each "Product" sheet has new price in F33

Code in "Product A" only (but will need to be in ALL sheets if testing proves it works: as you copy a sheet, then the code will move as well))

Please Login or Register  to view this content.

In "Raws" (basically the same code)

Please Login or Register  to view this content.

In a general module

Please Login or Register  to view this content.

At this stage do not worry about the code.

For testing, change data in "Raws" so all sheets should change and change "Secondary costs" in "Product A" to test these changes.

Wow! That worked. I can't even pretend to understand why yet. That was very nice of you to take all that time to do this. I really appreciate it. I can tell that was not very simple.

I realised I needed another check in the code for the situation where a change in "Raws" has not changed a price so we do not update the "Summary" and thus retain the correct "Last Price". ( I like a challenge .... sometimes!)

Please Login or Register  to view this content.

Awesome! Thank you so much.

The only think I noticed is that when I copy and duplicate a new product tab, the hit the "List Sheets" button it does bring in the product name, but not the initial product price. Once I change the price then all the date shows up just not initially.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Got it. It's done

The "List Sheets" does just that: I'll look at the question of updating the "Summary" when "List Sheets" is run (at that point I had not given thought to the recent updates) .. but tomorrow!

No worries. You have plenty already. I don't mean to keep bugging you.

Minor change ..

Please Login or Register  to view this content.

Cool. thanks