http://chandoo.org/forums/tags/dashboards Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Thu, 23 May 2013 20:08:20 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/filtering-table-from-dropdown#post-104364 Thu, 23 May 2013 15:48:29 +0000 Faseeh 104364@http://chandoo.org/forums/ <p>Hi farrusete,</p> <p>Please upload the sample file for this!</p> <p>Regards, </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-104323 Thu, 23 May 2013 13:10:03 +0000 bgane 104323@http://chandoo.org/forums/ <p>Thanks for the welcome. I really enjoy all the information on this site, and the help through the forums. I do have a follow up, now that I have downloaded the example spreadsheet. I'm trying to determine how to make 'chtProductBreakup' see through. In other words, on the Interactive Sales Chart tab, on the 'Pick a year' section of the dashboard, best I can tell to the right of the years is a picture of the graph from the Calculations tab. However, I'm not sure how to make that picture see through, so the only thing that displays from the picture is the bars. Thanks for any light you can shed on this for me. </p> http://chandoo.org/forums/topic/filtering-table-from-dropdown#post-104165 Wed, 22 May 2013 23:58:43 +0000 farrusete 104165@http://chandoo.org/forums/ <p>Hi there,</p> <p>Its nice to read these forums and find answers to almost everything :). Tried to find something in which my dashboard could be based on with no success so here It is what Im thinking about and ill appreciate if someone could point me in the right direction.</p> <p>I have 2 sheets:</p> <p>sheet 1: Data table with:<br /> - sales date<br /> - sales rep<br /> - client<br /> - client group<br /> - product<br /> - amount</p> <p>sheet 2: the dashboard. I want to put a dropdown list with all unique "Client Groups", two "Year 1" and "Year 2" dropdown lists. When user selects data in those three fields, data table from Sheet1 should be filtered and then displayed below selectors as follows:<br /> <pre><code>- Client1 | Product1 name: (amounts) January Year1 | January Year2 | February Year1 | February Year2 | December Year 2 | Total - Client1 | Product2 name: (amounts) January Year1 | January Year2 | February Year1 | February Year2 | December Year 2 | Total - Client1 | Product3 name: (amounts) January Year1 | January Year2 | February Year1 | February Year2 | December Year 2 | Total - Client1 | Product4 name: (amounts) January Year1 | January Year2 | February Year1 | February Year2 | December Year 2 | Total .... n times - Client2 | Product1 name: (amounts) January Year1 | January Year2 | February Year1 | February Year2 | December Year 2 | Total - Client2 | Product2 name: (amounts) January Year1 | January Year2 | February Year1 | February Year2 | December Year 2 | Total - Client2 | Product3 name: (amounts) January Year1 | January Year2 | February Year1 | February Year2 | December Year 2 | Total - Client2 | Product4 name: (amounts) January Year1 | January Year2 | February Year1 | February Year2 | December Year 2 | Total .... n times</code></pre> <p>I can extract that info from Sheet1 by getting just the data that I need based on above dropdown values, but product and clients are ilimited and different in number from one client group to other so... is there some way to display that sortered data table or data from a pivot table in second sheet for me to reference this table and make further calculations / charts in the dashboard?</p> <p>Thank you so much in advance </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-103925 Tue, 21 May 2013 17:45:42 +0000 SirJB7 103925@http://chandoo.org/forums/ <p>Hi, bgane!<br /> Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.<br /> Regards! </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-103878 Tue, 21 May 2013 12:37:35 +0000 bgane 103878@http://chandoo.org/forums/ <p>That's it! I must be blind. Thanks for pointing me in the right direction! </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-103754 Mon, 20 May 2013 20:59:41 +0000 SirJB7 103754@http://chandoo.org/forums/ <p>Hi, bgane!<br /> This is what you're looking for?<br /> <a href="http://img.chandoo.org/vp/interactive-sales-chart.xlsm" rel="nofollow">http://img.chandoo.org/vp/interactive-sales-chart.xlsm</a><br /> If it is, the download link is just below the video of your posted link.<br /> Regards! </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-103728 Mon, 20 May 2013 19:42:20 +0000 bgane 103728@http://chandoo.org/forums/ <p>Here is the link: <a href="http://chandoo.org/wp/2012/05/09/interactive-sales-chart-in-excel/" rel="nofollow">http://chandoo.org/wp/2012/05/09/interactive-sales-chart-in-excel/</a> </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-103727 Mon, 20 May 2013 19:41:49 +0000 bgane 103727@http://chandoo.org/forums/ <p>Hi Hui,</p> <p>Thanks for the reply. I understand how to find the data, if I had the actual spreadsheet. However, the one I'm referencing is a sample dashboard, that Chandoo walks through on a video. Unless I overlooked it, I do not see a way to download the actual spreadsheet. </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-101609 Fri, 10 May 2013 06:47:08 +0000 oldchippy 101609@http://chandoo.org/forums/ <p>Hi James,</p> <p>I haven't got a Mac, but just Googled and it is on Insert &gt; Names, you will find it there. </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-101607 Fri, 10 May 2013 06:04:47 +0000 James Perry 101607@http://chandoo.org/forums/ <p>Hello Hui....I use a Mac. Can u please help locate the Name Manager on Mac.<br /> Many thanks. </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-101579 Fri, 10 May 2013 00:07:53 +0000 Hui 101579@http://chandoo.org/forums/ <p>Bgane</p> <p>Firstly, Welcome to the Chandoo.org Forums</p> <p>With Named Formula you can use the Name Manager available at Formulas, Name Manager to assist</p> <p>Open the Name Manager<br /> Select the Name 'plstRegions'<br /> Click into the Formula in the Refers to: box<br /> Excel will highlight the selected Named Formula data area </p> http://chandoo.org/forums/topic/interactive-sales-dashboard-example#post-101370 Thu, 09 May 2013 13:23:55 +0000 bgane 101370@http://chandoo.org/forums/ <p>I've watched the video from Chandoo on how the Interactive Sales Dashboard was created, but I still have some holes in my trying to create it myself, so I can learn from it moving forward. Could someone help me understand which tab/list is named 'plstYears' (I assume the same tab are subsequest lists would cover 'plstRegions' and 'plstProduts'. Same question for 'lstProducts'. Also, I captured the macro noted in the video, but I'm a little lost in how it's supposed to function (I'm a little weak on macros). Any help would be greatly appreciated, as I'm trying to broaden my interactive dashboard options. </p> http://chandoo.org/forums/topic/need-some-input#post-101012 Tue, 07 May 2013 18:50:03 +0000 Faseeh 101012@http://chandoo.org/forums/ <p>Hi charl!</p> <p>Glad you found it!</p> <p>Regards, </p> http://chandoo.org/forums/topic/need-some-input#post-101006 Tue, 07 May 2013 18:37:18 +0000 SirJB7 101006@http://chandoo.org/forums/ <p>Hi, charl!<br /> Maybe you want to share your solution with the community, so as people who read this would have the issue or question and the solution or answer as well.<br /> Regards! </p> http://chandoo.org/forums/topic/need-some-input#post-100838 Tue, 07 May 2013 07:29:58 +0000 charl 100838@http://chandoo.org/forums/ <p>All working well now. Turns out it was a lot easier than I first imagined.<br /> C. </p> http://chandoo.org/forums/topic/need-some-input#post-100826 Tue, 07 May 2013 06:43:01 +0000 charl 100826@http://chandoo.org/forums/ <p>Hi there everyone.</p> <p>I track multiple Adwords accounts and look at data on a daily/weekly/monthly level. So I am always looking for interesting ways of simplifying what I'm looking it, but still getting the best possible results. But usually simplifying means doing a few complicated things first ;)</p> <p>One thing I am pondering something at the moment and would like some input.</p> <p>If I'm looking at data on a weekly breakdown. See the sample set of data here, <a href="https://docs.google.com/spreadsheet/ccc?key=0AoOkbv7dDXPsdG1ZOFp6UHN2NDdjOHBwcWNYbDl1aXc&amp;usp=sharing" rel="nofollow">https://docs.google.com/spreadsheet/ccc?key=0AoOkbv7dDXPsdG1ZOFp6UHN2NDdjOHBwcWNYbDl1aXc&amp;usp=sharing</a></p> <p>What I would like to do is create a Value metric for each week. So in a week the values will be compared with the other values and points will be assigned to that week. This way I can get an overview of a week taking into account all the key metrics in one figure.</p> <p>As in the sample data I have attached notes to the values with the corresponding values. The week with the lowest number of clicks will get 1 point, and every higher value will get 1 more. The number of points is dependant on the number of weeks. This adds more weight to the values.</p> <p>How would I go about getting this process automated? Massively long formulas that will give me a headache or should I set up an additional table which tracks the figures and allocates points based on the results?</p> <p>Hope this all makes sense. :)</p> <p>*UPDATE*<br /> Looks like I'm on the right track using the RANK formula </p> http://chandoo.org/forums/topic/dashboard-with-multiple-scenarios#post-89193 Thu, 04 Apr 2013 11:40:13 +0000 HBop 89193@http://chandoo.org/forums/ <p>Hi,</p> <p>I was wondering if anyone had any tips as to how I could create a dashboard similar to the one in the link (in the Summary tab) in Excel.</p> <p><a href="http://sdrv.ms/13TSyLr" rel="nofollow">http://sdrv.ms/13TSyLr</a></p> <p>Basically I want to display up to 4 different scenarios (the user can select whatever scenarios they want to see based on the parameters in the data)that can be easily compared in a summary table (and graph). I've put some dummy data in the Data tab to explain what I mean. Crucially, I need the user to be able to select multiple values for a particular field if they wish.</p> <p>I know I can use pivot tables for this (like in the 'Group A Pivot' tab) but it's not ideal as the user won't able to compare the 4 data sets very easily. Also my real data has far more parameters than I've put in here so it'll be a bit of a mess!</p> <p>If someone could give me some advice or point me in the direction of another post that covers this (I've had a look but I might have missed something) it would be most appreciated. I'm using Excel 2007.</p> <p>Thanks in advance! </p> http://chandoo.org/forums/topic/calculations-across-columns-vs-single-column#post-88383 Mon, 01 Apr 2013 12:55:04 +0000 gwstudent 88383@http://chandoo.org/forums/ <p>That's brilliant. Thank you so much! </p> http://chandoo.org/forums/topic/calculations-across-columns-vs-single-column#post-88259 Mon, 01 Apr 2013 02:42:17 +0000 Hui 88259@http://chandoo.org/forums/ <p>I think this does it<br /> P16: <code>=IF(AllVC1&gt;0,1,IF(AllVC2&gt;0,2,if(Allvc3&gt;0,3,0)))*SUMPRODUCT(($A$16:$A$182=$A16)*($C$16:$C$182&lt;=Q$15)*($D$16:$D$182&gt;=Q$15))</code> </p> http://chandoo.org/forums/topic/calculations-across-columns-vs-single-column#post-88231 Mon, 01 Apr 2013 00:02:02 +0000 gwstudent 88231@http://chandoo.org/forums/ <p>Hui -</p> <p>First of all that's genius and that works if I want to sum all of the columns but is there a way to get *exclusive* results? So, if the VC1 column is true then display 1 OR if VC2 column is true then display 2 OR if VC3 column is true then display 3? Again, I realize there are overlapping dates so if VC1 and VC2 are both true then take VC! as that occurs first. </p> http://chandoo.org/forums/topic/calculations-across-columns-vs-single-column#post-87287 Thu, 28 Mar 2013 04:10:41 +0000 Hui 87287@http://chandoo.org/forums/ <p>Make the following changes:<br /> M16: =IFERROR(MATCH($E$16:$E$182,Dakine,0),0)<br /> N16: =IFERROR(MATCH($H$16:$H$182,Dakine,0),0)<br /> O16: =IFERROR(MATCH($K$16:$K$182,Dakine,0),0)<br /> Copy these 3 cells down</p> <p>P16: =SUMPRODUCT((AllVC1+AllVC2+AllVC3)*($A$16:$A$182=$A16)*($C$16:$C$182&lt;=P$15)*($D$16:$D$182&gt;=P$15))<br /> Copy across and down </p> http://chandoo.org/forums/topic/calculations-across-columns-vs-single-column#post-87269 Thu, 28 Mar 2013 02:52:35 +0000 gwstudent 87269@http://chandoo.org/forums/ <p>Hi - </p> <p>Yes. This spreadsheet is a mod of the vacation tracker that I have re-purposed. On the Tracking worksheet, Column P, row 60 there is a SUMIFS formula which works fine enough but it also needs to to check all the values in columns N and O (not just M) and populate the respective field with a 1, 2, or 3. I realize there is some overlapping with the dates - that's OK. Is there a workaround to the SUMIFS formula to check all 3 columns and not just column M?</p> <p><a href="https://docs.google.com/file/d/0B2V4Nkca2ASFNGhPZW8wRlc3cnc/edit?usp=sharing" rel="nofollow">https://docs.google.com/file/d/0B2V4Nkca2ASFNGhPZW8wRlc3cnc/edit?usp=sharing</a> </p> http://chandoo.org/forums/topic/calculations-across-columns-vs-single-column#post-87226 Thu, 28 Mar 2013 00:41:03 +0000 Hui 87226@http://chandoo.org/forums/ <p>Gwstudent</p> <p>Can you post a sample file with the layout your proposing? </p> http://chandoo.org/forums/topic/calculations-across-columns-vs-single-column#post-87183 Wed, 27 Mar 2013 20:50:48 +0000 gwstudent 87183@http://chandoo.org/forums/ <p>Hi -</p> <p>There is a great posting of a vacation dashboard here: <a href="http://chandoo.org/wp/2013/01/24/employee-vacations-tracker-dashboard/" rel="nofollow">http://chandoo.org/wp/2013/01/24/employee-vacations-tracker-dashboard/</a></p> <p>This solution stores all of the vacation information in a single column, that is the vacation type, start date, and end date are in their own respective single column, but would the dashboard and calculations still work if the start dates, end dates, and vacation codes were in different columns for each vacation type? </p> <p>The SUMIFS formula on the calculations worksheet goes down the list of vacation codes but if those vacations codes were in 4 separate columns what would be the correct method to use? </p> http://chandoo.org/forums/topic/best-way-to-show-interactive-training-schedule-to-trainees#post-86548 Mon, 25 Mar 2013 17:02:23 +0000 quantamara 86548@http://chandoo.org/forums/ <p>I have been using slicers and designing dashboards - thanks to the excellent video by chandoo. I am now trying to use those techniques to show classroom schedule to trainees. The trainees are spread across geographies and sessions are spread over many days.. i need to make it interactive such that when a trainee chooses a day, he can see the sessions he/she needs to attend...the center-piece of this interactive dashboard needs to be a dynamic chart that shows blocks representing session and the width of block being the duration... was wondering if this is doable?</p> <p>request guidance </p> http://chandoo.org/forums/topic/school-trail-balance-final-accounts#post-83160 Sun, 10 Mar 2013 18:08:47 +0000 Muhammad Shakeel 83160@http://chandoo.org/forums/ <p>thanks for the reply,</p> <p>i have not find any related illustration there.</p> <p>any one else? </p> http://chandoo.org/forums/topic/school-trail-balance-final-accounts#post-83042 Sat, 09 Mar 2013 16:21:42 +0000 Faseeh 83042@http://chandoo.org/forums/ <p>Hi Shakeel,</p> <p>Please visit this link:</p> <p><a href="http://www.excelbanter.com/showthread.php?t=136703" rel="nofollow">http://www.excelbanter.com/showthread.php?t=136703</a></p> <p>Regards,<br /> Faseeh </p> http://chandoo.org/forums/topic/school-trail-balance-final-accounts#post-83036 Sat, 09 Mar 2013 15:13:02 +0000 Muhammad Shakeel 83036@http://chandoo.org/forums/ <p>Hi firends!</p> <p>Can you please provide me any site link or any sample file for School accounting, maintaining</p> <p>&gt;&gt;&gt; Ledger<br /> &gt;&gt;&gt; Trial Balance<br /> &gt;&gt;&gt; Final Accounts<br /> &gt;&gt;&gt; Dash Board if any please<br /> &gt;&gt;&gt; Or any kind of school accounts in Excel sample or example work book<br /> I will be highly thankful to you all for you kindness.</p> <p>Thanks &amp; Regards<br /> Muhammad Shakeel </p> http://chandoo.org/forums/topic/check-if-entry-exists-if-yes-update-if-no-create#post-75476 Thu, 31 Jan 2013 09:18:35 +0000 MikeLanglois 75476@http://chandoo.org/forums/ <p>Hi Nara,</p> <p>Was able to download and check that file and it works perfectly. So far it is doing everything that is needed!</p> <p>Thanks very much for your help! :D</p> <p>Mike </p> http://chandoo.org/forums/topic/excel-scrollable-charts#post-75056 Wed, 30 Jan 2013 03:03:32 +0000 Hui 75056@http://chandoo.org/forums/ <p>Rico</p> <p>Firstly, Welcome to the Chandoo.org Forums</p> <p>When you say Scrollable Charts I assume you mean that by using a Slider or other Control that when the scroller is moved the chart changes?</p> <p>It is entirely possible to both:<br /> Have 1 set of controls that controls many charts or<br /> Have multiple controls that control multiple charts.</p> <p>The system is </p> <p>1. Have a data storage area/s<br /> 2. Setup Report Areas or Extract Areas that extract data from the data store and can be changed by use of Controls<br /> 3. Add the controls as required<br /> 4. Add the charts which are based on the Report Areas, not the original data</p> <p>So that now as the controls are changed the Data Extract areas update and the charts update.</p> <p>The above can all be done without VBA using Excel.</p> <p>For more specific requests you may want to consider posting a sample file: Refer to the Green Sticky posts at the main Chandoo.org/Forums page</p> <p>You may want to consider reviewing some of the Excel Dashboard Challenges that are also posted here as they have many examples of how to achieve these goals using a variety of controls and layouts. </p>