Financial year callendar to return total values based on week number

So I added a few columns to make a simple SUMIFS instead of a slick SumProduct but either will work.

I also added additional trigger cells that I made light grey, which you would hide. I left them grey so you can easily see what I included.

Also note that your bar graphs under the VS Last Week doesn't make sense. I don't know what summing a percentage will do for you.

DashBoard.xlsx


Let me know if you have any questions.


Also, I would likely NOT use sumifs, I would probably use a pivot table and slicers to then do REALLY REALLY REALLY simple Vlookups off the pivots to populate these tables.

Well SUMIFS are cool, and work correctly, the issue is when you get lots of them with multiple criteria they can start to bog the spreadsheet down.

So typically what I do is figure out what the lowest level of granularity is and make a pivot AT that level of granularity. Then you can do a Vlookup or Index+Match on the pivot instead of the raw data, but thinking about it again, your file doesnt have that many so I probably wouldn't bother.


Let me know if you have any other things you would like help with.

Thanks!

Another thing, when I did =WEEKNUM([@DATE])-5 (to calculate financial year which starts at week 1 commencing on 01/02/2015) all month of January shows as a minus. For example Date: 20/01/2015 will be showing up as 2015 (Year) -1 (Week) 1 (Month). Do you know how to fix this, so if it's Date: 20/01/2015 Week number would be 3. If dare for example 1/02/2015 it would be week 1?

Also it don't calculate BAGS, ACC, JEWELLERY on the dash board and I can't figure out why :/

You probably have an incorrectly named criteria for the BAGS ACC and Jewelry.

I still dont understand why you are subtracting 5 from week num, but if you cant get the weeknum to work, you might have to create a date to week number reference table than just reference the table

Well it appears Jewelry has two Ls on your dashboard page in cells C11 and J11. Fix that and it works.

You have mispelled the word jewelry with jewellry. Remove the second L in the cells listed above.

Originally Posted by freedox

Firstly, on the main sheet there are greyed our fields around the main dashboard. Grey fields are unavailable to edit or do anything with them, do you know how to remove that?

I am assuming you mean the grey cells with nothing in them, just select them and choose NO fill color. Otherwise I have no idea what you are talking about.

Originally Posted by freedox

Another add on to the sheet I need to do is to show text. In data entry I have added New Brands Launched and in the quantity there will be brand name entered. So in dashboard I need to show (somewhere) the brand names for that week. So in general should be the same formula used for calculating sumifs but instead of sum there should be show?
Attachment 390401

I don't see ANY data in this workbook that has Brand Names.

The first attachment doesn't work, and the second attachment has nonsense in the quantity column.

Yikes, that is a terrible design. You want a standardized dataset where each column houses ONE data measure or data dimension. Start a new column for the new stuff is what I would do, but then I would probably redo the entire thing to use pivot tables instead of sumif formulas because I do not want to use an array to capture the unique values in the week you are looking at to pull in the names of the new stuff...

I would make a pivot table then use a slicer to control the pivot instead of the sumifs using the form controls you are using.

Unfortunately I get on here when I am waiting on something at work, and I have a busy week so I can't promise I will be able to pull something together for you in the near future.

Sorry, what are you wanting?

Well the thing I said is the best is always having a standardized dataset where each column is used in the pivoting of the data. Instead of having some calculations on the dashboard to pull in week 15, you have a column for week number in the dataset, so then its much more simple to pull the data into the dashboard.

instead of putting nonsense in a quantity column (like josehp, kichenwear, KG) start another column for whatever that is, because it is CLEARLY not a quantity.

Also, your data lists tab is unclear as I don't understand what makes a week a -5 for example.


Anyway, what I did works for you, just add a column for whatever thing you are adding to the bottom of quantity, since it is not a quantity and everything should be fine

Sorry, I get on here from time to time when waiting on meetings or data.

I still have trouble trying to understand exactly what it is you are trying to accomplish.
If I were you, I would create a new post with the very specific request and see if we can help.