http://chandoo.org/forums/ Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Tue, 21 May 2013 13:07:29 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/validation-rules-in-access#post-103881 Tue, 21 May 2013 12:48:46 +0000 Satish.digit 103881@http://chandoo.org/forums/ <p>Hi,</p> <p>Validation rules are not working in access form for first time user. but its working after key some thing in the field.</p> <p>Can anybody help me on this.</p> <p>Regards,<br /> Bob. </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/matchmax-formula#post-103875 Tue, 21 May 2013 12:22:18 +0000 Afarag 103875@http://chandoo.org/forums/ <p>Dears,<br /> i want to know using match&amp;max formula<br /> EX:<br /> Ahmed-------44<br /> Islam-------78<br /> Hosam-------20<br /> Osama------100<br /> I want to get the name who achieve the maximum value<br /> and i want to get the top second name who achieve the high value </p> http://chandoo.org/forums/topic/cash-flow#post-101278 Thu, 09 May 2013 06:10:43 +0000 James Perry 101278@http://chandoo.org/forums/ <p>Hi guys...<br /> I need help with a Line Chart/Graph Problem. I have following problem.<br /> I want to represent cash flow at the end of the each month.<br /> Below is my data.<br /> For example at the end of month-2, net cash flow is (165,070) But suddenly a payment comes thru from a client.<br /> So finally becomes my cash flow at the end of month-2 is (76,482).<br /> So there are 2 amounts for month-2 (X axis).<br /> Likewise....how do I do a graph???</p> <p>(79,630) Month-1<br /> (165,070) Month-2<br /> (76,482) Month-2<br /> (128,422) Month-3<br /> (36,608) Month-3<br /> (97,848) Month-4<br /> (41,836) Month-4<br /> (78,326) Month-5<br /> (12,258) Month-5<br /> (86,958) Month-6<br /> (47,338) Month-6<br /> 55,718 Month-7</p> <p>Thank u very much. </p> http://chandoo.org/forums/topic/sorting-of-data-based-on-critera#post-103609 Mon, 20 May 2013 07:59:04 +0000 khelgadi 103609@http://chandoo.org/forums/ <p>Dear All,<br /> Please go through the attached sample file. </p> <p><a href="https://docs.google.com/file/d/0B0W5ZzWOyj6TV0ZpYnBNVHNmNUE/edit?usp=sharing" rel="nofollow">https://docs.google.com/file/d/0B0W5ZzWOyj6TV0ZpYnBNVHNmNUE/edit?usp=sharing</a></p> <p>The Data sheet contains the process steps while result sheet shows the batches ( in process) at every dept.<br /> I have tried using rank command, but failed to do so. You Excel Gurus are requested to help me out.<br /> Thanks </p> http://chandoo.org/forums/topic/cut-data-from-main-sheet-to-a-new-sheet-according-to-a-specific-criteria#post-103869 Tue, 21 May 2013 11:49:40 +0000 amr@baseqat.com 103869@http://chandoo.org/forums/ <p>I have a main sheet contains data of 100s employees in different departments. I would like to open new work sheet and enter the name of the department in a specific cell and after that the full data of all employees of that department got copied from the main sheet and past in this new sheet in the same order. How can i do this ? </p> http://chandoo.org/forums/topic/macro-to-consolidate-workbooks-into-one-work-book#post-103853 Tue, 21 May 2013 10:27:47 +0000 SP 103853@http://chandoo.org/forums/ <p>Hello,</p> <p>Can anyone help me on my issues? </p> <p>I have to consolidate different work book in to one. </p> <p>Each work book has one sheet and each sheet should get copied to one workbook.<br /> Maximum number of sheet is 3. I do it every hour can you please let me know if I can get a macro to do this task for me?</p> <p>Thanks in advance.<br /> SP </p> http://chandoo.org/forums/topic/maxif#post-103856 Tue, 21 May 2013 10:48:05 +0000 beroth 103856@http://chandoo.org/forums/ <p>Is there a formula that functions as SUMIF but returns the MAX of the sum_range? </p> http://chandoo.org/forums/topic/updated-post-please-read#post-103549 Mon, 20 May 2013 01:13:59 +0000 Hui 103549@http://chandoo.org/forums/ <p>To All</p> <p>Please note that the main Posting Rules and Etiquette sticky post has been renamed and updated<br /> <a href="http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting" rel="nofollow">http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting</a></p> <p>Please take 5 mins to read.</p> <p>Thanx</p> <p><strong>Hui... </strong> </p> http://chandoo.org/forums/topic/moving-average-in-a-column#post-101952 Mon, 13 May 2013 02:55:24 +0000 Kirit George 101952@http://chandoo.org/forums/ <p>Hi,</p> <p>I wanted to calculate the moving average in data which is updated weekly. Wanted to get the latest 3 weeks' average.</p> <p>The data is as follows:<br /> <pre><code>Latest 3 Averages Name Average Accuracy WE May 1 WE May 7 WE May 14 WE May 21 WE May 28 CE NCE Minor User 1 98.86666667 97 100 99.6 99.4 99.5 2.666666667 15 27 0 15 35 4 20 25 4 15 30 1 20 25 0 15 30 User 2 99.5 99.5 99.3 99.7 99.3 99.7 3.333333333 14 37 6 15 30 4 20 25 0 15 30 1 20 25 0 15 30</code></pre> <p>Requesting a solution from you to calculate the moving average. The criteria is as follows:</p> <p>1. Want to calculate the moving average from the data that is entered in the columns on the right side on a weekly basis. The data is to be entered under the column Latest 3 Averages, in the column B4 and B6 and so on which is fixed and should get updated.<br /> 2. The data below average accuracy has 3 different cells, have to calculate the moving average for all the three moving values. B5 should have the average from E,H,K,N,Q and so on. C5 should have the average from F,I,L,O,R. D5 should have an average from G,J,M,P,S.<br /> 3. Should have the specification to exclude the spaces or blanks and should not be included in the calculation. </p> http://chandoo.org/forums/topic/vlookup-wild-cards#post-102873 Thu, 16 May 2013 15:13:04 +0000 Chart68 102873@http://chandoo.org/forums/ <p>Hi,</p> <p>Im trying to lookup the contents of a cell but I know the contents of the cell will not be an exact match, is there anyway I can use a wildcard or has someone got a better way of doing this</p> <p>My formula is =VLOOKUP(C4,SM_CC!B:C,2,FALSE) C4 holds the text Pinewood but the column Im looking for holds Pinewood Avenue, so as its not an exact match Im returning a N/A.</p> <p>Ive been trying to fix this for ages but cannot find a way of doing this so would be extremely grateful for a solution.</p> <p>Kind Regards</p> <p>Claire </p> http://chandoo.org/forums/topic/transferring-worksheetsworkbook#post-103813 Tue, 21 May 2013 07:06:02 +0000 James Perry 103813@http://chandoo.org/forums/ <p>Hello friends of Chandoo.org.<br /> Here's my Q, based on a problem i just encountered.</p> <p>I want2 transfer (NOT copy) 9 worksheets from WorkBook #1 to WB #2. How do i do it IN ONE MOVE. ???<br /> Alternately, I can transfer the entire WorkBook, that is; transferring WB #1 to WB #2. Again.....how do i do it.</p> <p>Regards &amp; Best Wishes.<br /> James </p> http://chandoo.org/forums/topic/need-help-for-dynamic-graph#post-103816 Tue, 21 May 2013 07:18:12 +0000 SP 103816@http://chandoo.org/forums/ <p>Hello,</p> <p>I am working on Excel Graphs, I need a graph to show three years data 2011, 2012 and 2013. For the past 2 years (2011 and 2012) I have data available for all the months but for the current year I get the data end of every month. Now I want my graph to take (plot) the data only for the available months and I don't want it to dip when there is no data for a particular month. Can I get a solution to this problem?</p> <p>Regards,<br /> SP </p> http://chandoo.org/forums/topic/macro-to-resize-objects-according-to-certain-parameters#post-103014 Fri, 17 May 2013 08:10:22 +0000 Chuckles12 103014@http://chandoo.org/forums/ <p>Hi all,</p> <p>First time poster, am super excited to have found my way to this site!</p> <p>I have a question regarding a report I am trying to make more automated. I have a pivot table in excel 2007 which I update regularly with new data. I have a template in powerpoint which I want to link from excel to powerpoint. </p> <p>I know how to do some basic getpivotdata to populate parts of the template on another sheet, which I have linked into powerpoint, but then I start to run into some issues.</p> <p>The issue I am having is trying to figure out a code which will adjust object sizes in excel (arrows to be precise, in regards to their width) based on the values they are pointing to. Basically, this template is reporting profits for different categories per individual clients, so an example would be if this sector was producing negative profits or zero, the arrow size width pointing to it would be much larger. If it was producing 100k in profits, it would be more narrow, etc.</p> <p>There are also a lot of clients, so I am trying to figure out then how to have a 2nd macro copy the image after a new set of client details has been inserted and copy it to a new slide in the same powerpoint show.. Looping through say 80 different clients / creating 80 new slides automatically.</p> <p>So it's basically 2 things I am trying to accomplish here, and I have no idea if using VBA can help with the object resizing based on certain cell parameters is possible? I am sure there is a way to loop through all the clients and paste each template into a new slide in the same show.</p> <p>I hope this is clear enough, any and all advice would be greatly appreciated!!</p> <p>Regards, </p> http://chandoo.org/forums/topic/generate-a-list-of-a-given-set-of-values#post-103826 Tue, 21 May 2013 08:14:44 +0000 jj253 103826@http://chandoo.org/forums/ <p>I'm trying to make a list of certain number of different letters: e.g. 2 "a"s, 3 "b"s and 5 "c"s, giving a a b b b c c c c c. I know how many of each number I need, but can't work out how to put this information into generating a list of them. (I would eventually like to randomise this list, e.g. to give a b b c a c c b c, if there's a way of doing this all in one step that would be great) </p> http://chandoo.org/forums/topic/copying-cells-from-one-sheet-to-a-new-sheet-automatically#post-102490 Wed, 15 May 2013 09:27:04 +0000 NimP 102490@http://chandoo.org/forums/ <p>Hi,</p> <p>I have created a sheet named master sheet where users can select options from a dropdown list such as Lone Parents or Disabled.</p> <p>I have created new sheets named after the selctions form the dropdown list on the master sheet.</p> <p>Is it possible to automatically copy rows of data depending on what is selceted from the dropdown list on master sheet to the respective sheets.</p> <p>Master sheet will act as the register which will be used to update information etc?</p> <p>Thanks in advance for your help and support </p> http://chandoo.org/forums/topic/comparing-chart-by-weekdays#post-103811 Tue, 21 May 2013 06:37:25 +0000 madocar 103811@http://chandoo.org/forums/ <p>Hello everyone,</p> <p>I would like to ask you to help me. The thing is that I want to compare last two weeks by weekdays. It means I want to compare some values as monday to monday, tuesday to tuesday in "Line with markers chart". So basically having 7 columns is the best option.<br /> I don't know how to make "monday" as a specific parameter that has 2 values<br /> Perhaps It would be better to upload the file here but it is not possible right?</p> <p>Thanks </p> http://chandoo.org/forums/topic/percentage-question-issue-is-getting-different-values#post-81517 Fri, 01 Mar 2013 17:39:39 +0000 Skipper John 81517@http://chandoo.org/forums/ <p>I am getting different values, in the hundredths, when using two methods to calculate percentages. I want to have a template worksheet that is formula-based. So, when I use the formula =cell name/cell name; there is difference in values, again in the hundreds and thousands, between the formula =value/value. Both cells are formatted to number, 2 decimal points, percentage.</p> <p>What is going on here?</p> <p>I have already done the simple look for values that are sneaking in and software updates. I am using mac:office 2008 </p> http://chandoo.org/forums/topic/welcome-to-phd-forums#post-2 Sat, 25 Jul 2009 22:19:40 +0000 Chandoo 2@http://chandoo.org/forums/ <p>Hi all..</p> <p>After weeks of thought, finally I have yielded to it. </p> <p>...</p> <p>I have finally eaten a whole half kg of ice cream that is... </p> <p>Well, not really, I have created the forum on PHD. People have been emailing me and asking me "where can I ask a question?" Often telling them to "go bing or google" seems like an idea. But not all problems are solved and that is why we have this forum.</p> <p>I feel very excited to get this forum ready. I think forum is much more relaxed and fun place than a blog. we can write in a more casual and simple way and connect with each other. </p> <p>So ... Welcome to PHD forums.. Please tell me about yourself... </p> http://chandoo.org/forums/topic/watermark#post-103803 Tue, 21 May 2013 05:45:32 +0000 Balaji.N 103803@http://chandoo.org/forums/ <p>dear Sir</p> <p>how to remove a watermark in excel </p> http://chandoo.org/forums/topic/reminder-macro#post-103671 Mon, 20 May 2013 14:05:50 +0000 prasoo 103671@http://chandoo.org/forums/ <p>Hi Team,</p> <p>I need a macro where where it reminds every 2 minutes. </p> http://chandoo.org/forums/topic/how-to-organize-data-for-report#post-99763 Thu, 02 May 2013 15:15:31 +0000 Kimber 99763@http://chandoo.org/forums/ <p>I have financial data from a manually entered sheet provided by another department. I need to subtotal expenses by category for all products as well as by product for all categories. From the example you will see that a category was not provided for every product. This is an urgent need for my boss, and I'm stumped as to how to accomplish this most efficiently and expediently.</p> <p>I have created another spreadsheet listing an entry for each category for each product and listed "0". Beyond that, I'm stalled. The link is for the "raw" data I received.</p> <p><a href="https://www.dropbox.com/s/12b7ss2kh7i677o/recap.xlsx" rel="nofollow">https://www.dropbox.com/s/12b7ss2kh7i677o/recap.xlsx</a></p> <p>This is my first time uploading a file. Please let me know if i have made an error. Thank you for your help! </p> http://chandoo.org/forums/topic/addiong-numbers-in-excel-sheet#post-103781 Tue, 21 May 2013 02:42:50 +0000 reachkiran03 103781@http://chandoo.org/forums/ <p>How to add numbers entered in column and sum it up in excel sheet/ Like 2+3 =5 </p> http://chandoo.org/forums/topic/countif-or-sumproduct-i-dont-know#post-103720 Mon, 20 May 2013 19:09:05 +0000 Parts Guy 103720@http://chandoo.org/forums/ <p>Hi,</p> <p>I have a range of part numbers that have current sales in column B. The current On-Hand (O.H.) is in column C.</p> <p>I am trying to count the number of Part Numbers that have "0" O.H. only if they have a yearly sale of between 4 and 12.</p> <p>So in my head, here is the formula. Count the "0"'s in column "C" if the Sales from B1:B6 are &gt;4 and &lt;12. Here is an an example below.<br /> <pre><code>Part Number, YRSL, O.H. 123, 13, 1 124, 9, 0 125, 8, 0 126, 5, 1 127, 5, 0 128, 3, 0</code></pre> <p>In this example the answer is "3". There are 3 part numbers (124,125,127) that have sold between 4 and 12 times that have a current O.H. of "0".</p> <p>I have a sample file I can send if necessary.</p> <p>Thanks,</p> <p>Jeff </p> http://chandoo.org/forums/topic/countifs-on-another-column#post-103579 Mon, 20 May 2013 04:58:25 +0000 gina clemens 103579@http://chandoo.org/forums/ <p>Hi,</p> <p>I have a large set of data I am working on which contains alot of moisture data. Within my huge set of data there are moisture readings that come from various treatments. I am trying to do averages on a bunch of these different treatments for which I am using an averageifs statment which is working nicely - but... in quite a number of cases there is 1 of, for example 5, of the moisture readings for a particular treatment missing - in which case I want to just have an empty cell as the average of the 4 remaining moistures may not be representative. </p> <p>So I would like to do something that works excatly like the averageifs but rather than giving me the average it says that eg 4 cells make up the average - so a count. But countifs seems to work differently as I can't tell it to count all the cells in column H that meet meet critera 1 in coulumn A, critera 2 in Coloum B and Critera 3 in Column C.</p> <p>I hope this makes sense. The idea is that I can eventually tell have a formula saying that if the count in column H doesn't = 5 (or whatever the number maybe for that treatment) then just leave the cell empty rather than giving me the average. </p> http://chandoo.org/forums/topic/can-this-be-automated-any-help#post-102331 Tue, 14 May 2013 19:02:08 +0000 joparo 102331@http://chandoo.org/forums/ <p>hi, is this possible to automate, every day i get like 2000 company names of NGOs across usa, in excel, i have to copy each company name and put it in google and get the URL...is it possible to automate it through excel programming or macros. </p> http://chandoo.org/forums/topic/using-if-to-return-the-text-name-of-a-cell-with-a-certain-rank#post-101215 Wed, 08 May 2013 19:57:26 +0000 greykitten 101215@http://chandoo.org/forums/ <p>Hi,<br /> I need to use the IF formula to return the name of a cell that has a certain rank. I have ten cells with text words in them and each has a rank 1-10. And I need the names to populate in a column in order of rank. I can't figure out how to write this in the syntax. I've uploaded the file where I'm trying to do this (see link below). Thank you!!</p> <p><a href="https://hotfile.com/dl/215673014/3706707/RANK_Practice.xlsx.html">https://hotfile.com/dl/215673014/3706707/RANK_Practice.xlsx.html</a> </p> http://chandoo.org/forums/topic/using-rank-formula-to-find-top-ten-largest-values#post-103743 Mon, 20 May 2013 20:12:43 +0000 greykitten 103743@http://chandoo.org/forums/ <p>Hi,<br /> I'm trying to find the top ten largest values in a list. Currently, the formula in place retrieves the top ten items in order of lowest value. How do I modify the formula to retrieve the top ten largest numerical values in order (top item - largest value down to item with the lowest value)?? Thank you.</p> <p><a href="https://hotfile.com/dl/223735286/abd34d7/Using_Rank_To_Find_Largest_Item.xlsx.html">https://hotfile.com/dl/223735286/abd34d7/Using_Rank_To_Find_Largest_Item.xlsx.html</a></p> <p>Your help is greatly appreciated,<br /> greykitten </p> http://chandoo.org/forums/topic/fixed-filter-on-pivot-tables#post-103719 Mon, 20 May 2013 19:08:13 +0000 martinpaiva 103719@http://chandoo.org/forums/ <p>Hi All,</p> <p>I'm working with a pivot tables and I would like to filter with all the values without a value.. for example HaveTicket &lt;&gt; "No".</p> <p>the problem is I need to setup this filter after have data in the source table; becuase I lose the filter when the pivot table is empty.</p> <p>There is a way to Fix a filter if exist or not data in the pivot table? </p> <p>Thanks! </p> http://chandoo.org/forums/topic/using-vba-to-find-a-range-of-cells#post-103677 Mon, 20 May 2013 14:48:08 +0000 Montrey 103677@http://chandoo.org/forums/ <p>Hey Guys, I've search high and low for what I'm trying to do and can't seem to find it.</p> <p>I guess my main problem is that the range(s) I need to copy, can change in size. So what I'm thinking I need to find the cell where my range starts, and then find the cell where my range ends(there is always a blank row before the last row) and then I need to select all the rows in between and including the start and last cells rows.</p> <p>In sum; I have a masterworksheet that has data for different departments. I need to copy &amp; paste value each departments info into their own separate workbooks and save each file as the name of the department and a specified date. I then have to send each file to the specific departments contacts through Outlook.</p> <p>I have to do this everyday, so I've been trying to figure out some VBA to do this. I've just been struggling on how to even get started, which would be to find the ranges i need to copy.</p> <p>Thanks guys! </p>