I have a workbook with separate sheets for each product (about 20 sheets, adding more on a regular basis). Each product is only available for a certain time frame, and has daily sales data recorded on that product's sheet. I want an overall snapshot across all products from any given date to be consolidated on a new sheet. This would sum from a particular column on each of the other sheets if a corresponding date exists.

I have a moderately passable function right now that has a separate VLOOKUP for each product sheet like SUM(IF(ISERROR(VLOOKUP(DATECELL,SHEETNAME!ARRAY,COLUMN... next VLOOKUP, next VLOOKUP etc., but it's incredibly cumbersome to update each function when a new product is added. I'm thinking there's a much easier way utilizing a named group (sheet names), SUMIF, VLOOKUP etc. Then when a new product sheet is added, I can simply add the sheet name to the named group rather than editing all the functions.

Any help would be much appreciated!

  • Have you looked into the SUMIF and SUMIFS fuctions? – CLockeWork Jun 3 '14 at 14:54
  • I have, it's combining it with vlookup (since the location of the particular date and therefore the data that needs pulling will be different on each sheet--or may not even exist) and the named group that I'm not sure of. – user329005 Jun 3 '14 at 15:16
  • OK, to help make sure you get the best answer can you detail exactly why you can't have all the data in one table? – CLockeWork Jun 3 '14 at 16:00
  • Thanks for your assist, btw. The data is daily sales data for each product, which includes advertising cost, items, profit, etc for each day. Recording all of this data for all of the products for every date on one table would be much less user friendly and less organized, especially when several new products are added each week, and may only last for a week. – user329005 Jun 3 '14 at 16:11
  • 1
    No problem, btw :) Having it on one table would be a question of making a raw data table, and then using Pivot Tables for agregated views and user friendly data presentation. Do you store and report on expired products? – CLockeWork Jun 3 '14 at 16:21

You are on a road to misery with the multiple sheets design. I would consolidate onto one table (Insert ribbon, Table), then use the filter buttons in the first row for quick searching, add Pivot Tables and Charts for reports etc.

If you continue with separate sheet approach, expect to waste endless hours on this issue and the next issue and the next one. As you yourself noted its "incredibly cumbersome".

| improve this answer | |
  • 1
    +1, multiple sheets with the same data is never a good idea – CLockeWork Jun 4 '14 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.