• 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.

Unable to Make a Dashboard - Cannot think out of the Box

R

rush2rajen

Member
Hi Excel Gurus,

I was trying to make a Dashboard but in vain, unable to think out of the box.
There are figures from Apr to Oct with Headings
Vendor ID, Item Id, Dept, Centre & Seven Sheets of Months from Apr to Oct.

What I need is a Dashboard which will show Top 20 Vendor Code of all the months from Apr to Oct, which means in April if there are Top 20 Vendor Code it is not possible that in May that same Top 20 will be there, they may change. No problem in that.

I want to see a Dashboard of Top 20 Vendor Code with Sum of Item ID and Department.
I want to check at one Glance that the top 20 Doctors from Apr to Oct and their Performance on How Much sale the vendor code had given from each dept itemwise.
I don't know whether I had been able to clear your doubts.


Thanks
Raj
 

Attachments

  • TOP 20.zip
    973.8 KB · Views: 15
Hi - I havent yet worked on it..however, see the attached and let me know if my understanding is correct...basis your inputs i shall start working on it...

In the attached..I am just assuming only 2 months data..i.e. Apr and May,,
 

Attachments

  • check.xlsm
    8.6 KB · Views: 21
Hi Asheesh,

A to D Column is perfect. But I don't want the H column as it is of no use for me. But I would request you one thing please don't use VBA as I don't know the same.



Thanks
Raj
 
Hi Asheesh,

The file is excellent. But I think the formula is too tough for me to understand.
Can you please help by making it more simpler so that I can understand. The formulas are very big & tough to understand

Thanks
Raj
 
Hi Raj -

@John() has given some feedback for the solution given above..go through the link below..

http://chandoo.org/forum/threads/di...-days-looking-for-top20-from-your-data.20572/

Some how I am unable to post the file here...however, you can use the below formula in C4 of "Dashboard" tab in the file attached in post no 7.

INDEX(INDIRECT($A$2&"!$A$2:$A$10000"),MATCH(0,COUNTIF($C$3:C3,INDIRECT($A$2&"!$A$2:$A$10000"))+(INDIRECT($A$2&"!$F$2:$F$10000")<>SMALL(INDIRECT($A$2&"!$F$2:$F$10000"),ROWS($A$1:A1))),0))

Once you paste this formula in C4, please acknowledge the formula with CTRL + SHIFT + ENTER and not just Enter

Let me know if you have any questions...
 
Nice one Asheesh .... just couldnt find this thread. First off hope you dont mind me downloading and going through the file.....
I am trying to teach myself more advanced excel and it really helps to work with stuff that is new to me and that I feel would be a benefit to me in the future.
so this file really suited those requirements.
I was able to break it down and understand how it worked even though its the first time I came across ... indirect ... before.

ran your updated code and it works perfect ... it now catches the duplicates ... will have to look at that now to see how it works as it seems to be a tricky thing to avoid duplicates pulling out wrong data. (did find a formula last night that seems very good and avoids the need for helper columns)

so rather than the original poster having to add a helper column and filter column F from lowest to highest your updated line of code dose the sorting perfectly.
 
Hi Raj ,

In the interest of efficiency you should think of replacing a formula such as :

=IF(SUMIFS(INDIRECT($A$2&"!$D:$D"),INDIRECT($A$2&"!$A:$A"),$C4,INDIRECT($A$2&"!$C:$C"),D$3)=0,"-",SUMIFS(INDIRECT($A$2&"!$D:$D"),INDIRECT($A$2&"!$A:$A"),$C4,INDIRECT($A$2&"!$C:$C"),D$3))

with the shortened version :

=SUMIFS(INDIRECT($A$2&"!$D:$D"),INDIRECT($A$2&"!$A:$A"),$C4,INDIRECT($A$2&"!$C:$C"),D$3)

The display for a zero value can surely be got using a custom cell format , such as :

General;General;"-"

Narayan
 
You must log in or register to reply here.
Back
Top