Thank you for clearing that up. It looked so simple on the pivot table alone that it should have worked. Will practice the dashboard in conjunction with the pivot now......thanks heaps....
http://chandoo.org/wp/2010/12/08/dynamic-dashboard-video-tutorial/
and scroll down to "Download Dynamic Dashboard Workbook" and click on the P1 sheet tab....and you will see what I mean when the Representative for R02 is filtered but does not appear in the pivot table....
yes, the workbook shows the same... and I am trying to replicate the results. it is the same data that I posted and I just can't seem to replicate it....so are you saying the field has to be checked in or order for it to work. I just thought it would be handy if you could filter from the field...
Yes, I know you can check the Representative checkbox and that will give the correct answer but on this website it has a pivot table that the representative box is not checked but the result is still 223 and that is why I don't understand how it should work. The spreadsheet was in the Dynamic...
Hi, I was working on an exercise on dashboards on this website which uses a pivot table. I am stuck on why my pivot table is not working. This is a simple pivot with an additional field name from the field list that is filtered but is shown in the pivot. It should take into account the filter...
I am using excel 2010 working with a pivot table. I have first and last names and their respective dates of birth together with their sales. two sales people have the same first and last name and different dates of birth and I want to see them on separate lines but when I put the last name...
I do want to see the distinct salesmen who sold specific products through the filter but when I try what you suggest I don't get the correct result. In my example I posted I should get the result of six salespersons. I get all ones and the grand total of one.
This is my data source. I have to use a pivot table to show the number of unique SalespersonID who sold products aaa1, bbb1,ccc1, ddd1 and eee1 during the period 01/01/2013 to 31/01/2013. The answer here should be six SalespersonID.
SalespersonID SalesDate ProductID DistinctSalesPersonID
111...
Hi, I have a pivot table with my source data being salespersonID, SalesDate and ProductID as the rows and count ProductID as the value. I have added another field to the source data with following formula =IF(COUNTIFS($A$2:A2,A2)=1,1,0) showing the distinct SalespersonID and added this field to...
Thanks Sajan,,,I had exactly as you had....it was just the columns needed to be widened....which is strange because my font is Arial 11 and for some strange reason the columns needed to be wider than usual otherwise I would get the hashes....thank you so much for your time and patience...I will...
Hi,forgive my questions....I am following your method to the letter...I am getting hashes in E2 where I am putting the formula....I am doing exactly as you have instructed....=IFERROR(INDEX(A:A, SMALL(IFERROR(IF(MATCH(IDList&DateList, UniqueID&UniqueDate, 0), ROW(IDList)), FALSE)...
Hi Sajan, I did ctrl shift enter originally but I am getting the ID "101" as the result. I do have A2:A6 as the named IDLIST and the unique lists are named correctly. I have the unique list in E2:F3 and G2 is where I am keying the formula in. Am I nearly there....
Hi Sajan, thanks for replying....I have the main list in A1:C6 and the unique list in A9:B11. I put your formula in E2,is that right? I get ######.
When you say(MATCH(IDList&DateList, UniqueID&UniqueDate, do you use the "&" as stated.
HiDebraj Roy, here is an example of what the data should look like:
ID…..Date……Value (Main List)
101…01/01/13…89
101…01/01/13…78
102…02/03/13…66
102…02/03/13…50
104…03/03/13…99
ID…..Date (unique list given to me)
101…01/01/13
102…02/03/13
ID…..Date……Value (this is how it should look)...
Hi Debraj Roy, sorry for delay,fell asleep..Having trouble uploading sample. Your attached example is nearly what I want. The way you show the Values hould be underneath each other like the main list. the result list should replicate the main list. Love your formula though.
Hi, I have a main list of 200 entries. The headings are ID, Date, value. The ID and Date will be duplicated at various times, however the value will be different. E.g.
ID…..Date……Value
101…01/01/13…89
101…01/01/13…78
102…02/03/13…66
102…02/03/13…50
I was given a smaller unique list of...