Hi People,
I'm starting the process of building a dashboard for my management. It's a sales function with about 20 people across three teams, and about 10-12 products to track weekly totals of
I have found that the data that exists (that I need to draw upon) is organized in an odd way (on different tabs of excel, with one tab for each month. This means that some months' tabs have 5 weeks of sales while others only have 4.. This in turn means, of course, that I can't just use the INDIRECT function to get past the tab constraint, unless I use some pretty crazy IFERROR functions which would make the strings iteratively longer and would mean that the spreadsheet is finite rather than being able to refer to the actual current data. I'm reluctant to do this since there must be an easier way, and I really want to use the automatic date filtering function of excel from next FY, so I'd like to redesign the format in which that data is entered & stored.
I'm thinking the best way is to have each weekly total of each product sold by each sales person on a new row in the one worksheet.
That way I can have pivot tables and VLOOOKUPS in an analysis layer as staging tables for the display layer.
Would you guys think I am on the right track with this thinking?
Can you see any possible issues with having it formatted as such:
(Column A) Week Ending Date | Robert | Product1 $ | Product2 $ | Product3 $
(Column A) Week Ending Date | James | Product1 $ | Product2 $ | Product3 $
(Column A) Week Ending Date | Albert | Product1 $ | Product2 $ | Product3 $
(Column A) Week Ending Date | Peter | Product1 $ | Product2 $ | Product3 $ etc....
Or, would it be better to organize it like this:
(Column A) Week Ending Date | Robert | Product1 $ |
(Column A) Week Ending Date | Robert | Product2 $ |
(Column A) Week Ending Date | Robert | Product3 $ |
(Column A) Week Ending Date | James | Product1 $ |
(Column A) Week Ending Date | James | Product2 $ |
(Column A) Week Ending Date | James | Product3 $ |
(Column A) Week Ending Date | Albert | Product1 $ |
(Column A) Week Ending Date | Albert | Product2 $ |
(Column A) Week Ending Date | Albert | Product3 $ | etc....
Just wondering whether a) this will do? or b) whether either would be advantageous or give me headaches.
Thanks!
-m.
I'm starting the process of building a dashboard for my management. It's a sales function with about 20 people across three teams, and about 10-12 products to track weekly totals of
I have found that the data that exists (that I need to draw upon) is organized in an odd way (on different tabs of excel, with one tab for each month. This means that some months' tabs have 5 weeks of sales while others only have 4.. This in turn means, of course, that I can't just use the INDIRECT function to get past the tab constraint, unless I use some pretty crazy IFERROR functions which would make the strings iteratively longer and would mean that the spreadsheet is finite rather than being able to refer to the actual current data. I'm reluctant to do this since there must be an easier way, and I really want to use the automatic date filtering function of excel from next FY, so I'd like to redesign the format in which that data is entered & stored.
I'm thinking the best way is to have each weekly total of each product sold by each sales person on a new row in the one worksheet.
That way I can have pivot tables and VLOOOKUPS in an analysis layer as staging tables for the display layer.
Would you guys think I am on the right track with this thinking?
Can you see any possible issues with having it formatted as such:
(Column A) Week Ending Date | Robert | Product1 $ | Product2 $ | Product3 $
(Column A) Week Ending Date | James | Product1 $ | Product2 $ | Product3 $
(Column A) Week Ending Date | Albert | Product1 $ | Product2 $ | Product3 $
(Column A) Week Ending Date | Peter | Product1 $ | Product2 $ | Product3 $ etc....
Or, would it be better to organize it like this:
(Column A) Week Ending Date | Robert | Product1 $ |
(Column A) Week Ending Date | Robert | Product2 $ |
(Column A) Week Ending Date | Robert | Product3 $ |
(Column A) Week Ending Date | James | Product1 $ |
(Column A) Week Ending Date | James | Product2 $ |
(Column A) Week Ending Date | James | Product3 $ |
(Column A) Week Ending Date | Albert | Product1 $ |
(Column A) Week Ending Date | Albert | Product2 $ |
(Column A) Week Ending Date | Albert | Product3 $ | etc....
Just wondering whether a) this will do? or b) whether either would be advantageous or give me headaches.
Thanks!
-m.