• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with a formula for work please.

ccguitar11

New Member
I have set up a sheet to track windshields sold per week. We have a weekly workbook and on the last page of each week is 4 tables from each week's workbook that tracks the top 10 windshields sold per week and how many. I would like a 5th table that has the top 10 for the month, but adds how many were sold for each top 10 for the 4 week period. So row E4-E52 is called count and this has 4 tables from each week, F4-F52 has the part # of the windshields. I just want that if in week one a DW2415 has 4 sold and week 2 has DW2415 at 3 sold, the monthly table would put DW2415 as one of the top 10 and count 7 for DW2415.
 

Attachments

  • PPE 11-07.xlsx - Excel 11_2_2021 2_36_27 PM.png
    PPE 11-07.xlsx - Excel 11_2_2021 2_36_27 PM.png
    123.8 KB · Views: 10
Attach a workbook please, pictures are no earthly use if you want this sort of solution.
 
Hi and welcome
You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyze and summarize or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

I adapted your database in the " Windhsled Tracking " sheet as an example You will of course to rewrite the formulas of course, but then things will be much easier for analysis like a simple Pivot Table.
By putting in some work at this stage you will save yourself a lot of trouble in the future
The same applies to your " Yellow sheet" which you can build as a simple continuous DB with all existing columns and a "date" column.
It's a lot of work, I do realize that :)
 

Attachments

  • Copy of PPE 11-07-1.xlsx
    203.1 KB · Views: 4
Back
Top