Pointy Haired Dilbert - Chandoo.org - Forums: Recent Topics

Pointy Haired Dilbert - Chandoo.org - Forums: Recent Topics http://chandoo.org/forums/ Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Pointy Haired Dilbert en-US Thu, 18 Mar 2010 14:41:10 +0000 http://bbpress.org/?v=1.0.1 <![CDATA[Search]]> q http://chandoo.org/forums/search.php RalphJ on "What does Conditional Formating really effect" http://chandoo.org/forums/topic/what-does-conditional-formating-really-effect#post-1189 Sun, 14 Mar 2010 14:37:49 +0000 RalphJ 1189@http://chandoo.org/forums/ Does conditional formating change the color index of the cell, if not what does it do? I am trying to count the colored cells of the top 10 numbers in a list that can actually be 10 to 14 numbers. Thanks RalphJ Hellcat94 on "Move Cells from one sheet to another" http://chandoo.org/forums/topic/move-cells-from-one-sheet-to-another#post-1221 Thu, 18 Mar 2010 04:00:47 +0000 Hellcat94 1221@http://chandoo.org/forums/ I am creating a spreadsheet that will allow users to update a "notes" cell with their comments. I want the main worksheet to keep only the latest 3 cells of data. These cells are in descending order, as a part of the same column. Whenever a new comment is added it should move the oldest to a second worksheet. The second worksheet would keep all of the archived notes. Is there a formula or set of formulas I could use to accomplish this task? ashfire jon on "IF this equals that OR that or that, then....." http://chandoo.org/forums/topic/if-this-equals-that-or-that-or-that-then#post-1217 Wed, 17 Mar 2010 17:00:55 +0000 ashfire jon 1217@http://chandoo.org/forums/ Hi all, i tried using this formula at work but couldnt get it to work and was intersted in what you guys would suggest. I got a work around using normal IF over 5 columns. Long term solution is to get it back in Access, which isnt my job :). =IF(F2=OR("AEC","DDH","externals","fec","quality","test & trials","unit 1","unit 1&2","unit 2","unit 3","unit 4","unit 5","unit 6","unit 6&7","unit 7","unit 8","unit amn","unit dgm","unit ferm","unit mrm","unit rtm"),"Construction",IF(F2=OR("electrical w/shop","ms","nbf","paint","plate prep shop","pmf","pps","","sms"),"Manufacturing",IF(F2=OR("nas","nas-annex","nas-outfit"),"Fabrication",IF(F2=OR("none budget","purchase part"),"Other","Another")))) Thanks BFC on "Sorting pivot tables on a "split" column" http://chandoo.org/forums/topic/sorting-pivot-tables-on-a-split-column#post-1215 Wed, 17 Mar 2010 16:05:37 +0000 BFC 1215@http://chandoo.org/forums/ Hi, The data for a pivot table is: Product Category Value A Cat1 17 A Cat2 13 B Cat1 60 B Cat2 40 C Cat1 85 C Cat2 25 If I now pivot the data so that the Product is on the left hand side (rows), Category forms the column headings and Value is the data - like Sum of Value Category Product Cat1 Cat2 Grand Total A 17 13 30 B 60 40 100 C 85 25 110 Grand Total 162 78 240 I want to swap these values to %ages of rows (easily done) but then sort the table by the percentage value of the Cat1 field into descending order. i.e. Sum of Value Category Product Cat1 Cat2 Grand Total C 77.27% 22.73% 100.00% B 60.00% 40.00% 100.00% A 56.67% 43.33% 100.00% Grand Total 67.50% 32.50% 100.00% My attempts so far amount to flattening the pivot table to text and then use it as the data source for a new pivot table which then allows me to insert a calculated field for the percentage of the Cat1 values and then sort on this field. I'm sure that there must be a way of doing this, without all my other extra work. Thanks in advance, Mike kedenham on "Backwards freeze panes" http://chandoo.org/forums/topic/backwards-freeze-panes#post-1214 Wed, 17 Mar 2010 14:08:46 +0000 kedenham 1214@http://chandoo.org/forums/ Hi - I was wondering if its possible within excel to do a 'backwards' freeze pains - so that the cells at the bottom stay the same if you scroll up and down? Have been told Windows Split may help but I'm not sure how this works. Kate lavkeshbhatia on "highlight top 80% values (sum) without sorting" http://chandoo.org/forums/topic/highlight-top-80-values-sum-without-sorting#post-1141 Sun, 07 Mar 2010 16:58:18 +0000 lavkeshbhatia 1141@http://chandoo.org/forums/ how do i approach this ? There are values in column A which are not sorted I need to highlight the top values which will add up to 80% of sum of all values in column A eg 15 25 18 5 7 8 12 10 adds up to 100 the output should highlight 25,18,15,12,10 A variation would be to highlight values at least till 80% (of the sum) is achieved barmacost on "Difference in Timestamps" http://chandoo.org/forums/topic/difference-in-timestamps#post-1204 Mon, 15 Mar 2010 22:27:56 +0000 barmacost 1204@http://chandoo.org/forums/ I have a column of date/timestamps with each row representing a new entry. I need to determine the difference between row 3 and row 2, row 4 and row 3, row 5 and row 4, etc. Row 2 2/10/10 9:33 Row 3 2/10/10 10:06 Row 4 2/16/10 18:37 Row 5 2/17/10 8:56 Row 6 2/23/10 16:39 Row 7 2/25/10 17:53 The catch is I need to exclude weekends from the calculation and I don't want the results rounded to the nearest day...I need to account for partial days as well. It doesn't seem that NetworkDays would be the right formula since it rounds to the nearest # of days. Do I need to build a separate table of just weekend days and exclude them with an index function or is there a formula I'm just not thinking of? Thanks Ozzy73 on "Embed image into a cell" http://chandoo.org/forums/topic/embed-image-into-a-cell#post-1207 Tue, 16 Mar 2010 10:32:31 +0000 Ozzy73 1207@http://chandoo.org/forums/ How do you embed an image into a cell? Thanks Ozzy73 on "Sorting Tables" http://chandoo.org/forums/topic/sorting-tables#post-1201 Mon, 15 Mar 2010 12:54:16 +0000 Ozzy73 1201@http://chandoo.org/forums/ What formula can I use to sort a numeric column in descending order & moving all its values with it in the rest of the table. The E.G is for a football tournament, the team with the most points should be on top. Group A P W D L GF GA GD Pts South Africa 3 2 1 0 3 1 2 7 Uruguay 3 0 2 1 3 5 -2 2 Mexico 3 0 1 2 4 6 -2 1 France 3 2 0 1 9 7 2 6 Thanks kedenham on "Chart Area" http://chandoo.org/forums/topic/chart-area#post-1200 Mon, 15 Mar 2010 12:45:12 +0000 kedenham 1200@http://chandoo.org/forums/ Hi. A really simple one - I want to expand the size of my chart area so that there's more space for the legend, but I want the actual chart to stay the same - but whenever I stretch it everything grows? Thanks! iswar10 on "Validation in Excel 2007" http://chandoo.org/forums/topic/validation-in-excel-2007#post-1188 Sun, 14 Mar 2010 10:29:03 +0000 iswar10 1188@http://chandoo.org/forums/ Your tips are really something to be appreciated! I have a problem!! I feel you are the guy for it!!! I am trying to do a nautical problem using Excel 2007. How would I allow user to input degrees and minutes in adjacent (on in different) cells? Since Latitude is not more than 90°, if the user input 90° in the degrees cell, then the minutes cell must go blank (or not to allow other than 0) If the user in put any integer between 0 and 59, allow the user to input between 0’ and 60.0’ in the minutes cell. I validate the degree cell easily i.e. any integer between 0 and 90. I have a problem in validating the minute cell. It needs a conditional validation, I feel, so the two cells together will give a result between 00° 00.0’ and 90° 00.0’. Please help! Thanks!! Dee on "Error in macro - Index refers" http://chandoo.org/forums/topic/error-in-macro-index-refers#post-1192 Mon, 15 Mar 2010 05:56:16 +0000 Dee 1192@http://chandoo.org/forums/ Dear All, I am getting the following error "Index refers beyond end of list" in the row presented within astricks (***)when i try to run this macro. I have two tabs called 'Project Managers' & 'Project List' in the file and at present i have only 4 names in Project Managers tab. The will grow in future. Sub getprojects() Dim Ftwbook As Workbook Dim Thiswbook As Workbook Dim LastPM, LastCell, RIndex, counter As Integer Dim Wksheet As Worksheet Dim Flatfilename As String Dim Filewithmacro As String Application.ScreenUpdating = False Flatfilename = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", Title:="Please choose a file") Filewithmacro = ThisWorkbook.Name Set Ftwbook = Workbooks.Open(Filename:=Flatfilename, ReadOnly:=yes) Set Thiswbook = Workbooks(Filewithmacro) ThisWorkbook.Worksheets("Project Managers").Activate Range("A1").Select LastCell = ActiveCell.SpecialCells(xlCellTypeLastCell).Row ReDim PM(LastCell) As String For counter = 0 To LastCell - 1 PM(counter) = ActiveCell.Value ActiveCell.Offset(1, 0).Activate Next counter Thiswbook.Worksheets("Project List").Activate Range("A4", Range("A4").SpecialCells(xlCellTypeLastCell).Address).Rows.ClearContents Ftwbook.Activate Ftwbook.Worksheets("Flat File").Select Range("Z4").Select LastPM = ActiveCell.SpecialCells(xlCellTypeLastCell).Row RIndex = 4 For counter = 0 To LastPM - 1 For Index = 0 To LastCell - 1 If ActiveCell.Value = PM(Index) Then ActiveCell.EntireRow.Copy Thiswbook.Worksheets("Project List").Activate ActiveSheet.Paste Destination:=Range(Cells(RIndex, 1), Cells(RIndex, 1)) Ftwbook.Worksheets("Flat File").Activate RIndex = RIndex + 1 Exit For Else End If Next Index ActiveCell.Offset(1, 0).Activate Next counter Application.CutCopyMode = False Ftwbook.Close SaveChanges = no Application.ScreenUpdating = True 'Call LastRowofPM End Sub Thanking you in advacne kedenham on "SWOT analysis bubble chart?" http://chandoo.org/forums/topic/swot-analysis-bubble-chart#post-1196 Mon, 15 Mar 2010 10:22:28 +0000 kedenham 1196@http://chandoo.org/forums/ Hi, I want to make a cahrt in excel that visually shows the outcome of a SWOT analysis. I like the idea of using bubbles to express it in excel - but I'm struggling to translate it into an actual chart. What I want to do is take 4 tables structured like the below information, and make the information appear in the correct quadrant of a matrix (which I can then label strengths, weaknesses, opportunities, threats using a text box) with the size of each bubble reflecting the weighting of the strength (the numbers in the right hand column below) The main bit I'm struggling with is how to make sure they all end up in the right quadrant? Kate Table 1 Strengths Strength 1 45 Strength 2 10 Strength 3 13 Table 2 Opportunities Opp 1 12 Opp2 9 Opp3 7 Table 3 Weaknesses Weak1 15 Weak2 6 Weak3 10 Table 4 Threats Threat1 12 Threat2 11 Threat3 10 Any help massively appreciated! If this is poorly explained - I want it to look like this: <a href="http://www.business-tools-templates.com/SWOT%20Chart.jpg" rel="nofollow">http://www.business-tools-templates.com/SWOT%20Chart.jpg</a> Thank you for any help!!! windyinnc on "Extracting Whole Address to Multiple Cells" http://chandoo.org/forums/topic/extracting-whole-address-to-multiple-cells#post-1152 Wed, 10 Mar 2010 14:25:53 +0000 windyinnc 1152@http://chandoo.org/forums/ Darn if I cannot figure this out. I kindly ask for this forums collective "guru" knowhow. I use the =left and =right formula all the time but only for easy extractions nothing overly complex. I run many queries which report back the entire stores address within one cell. Is there a formula I can use which will extract the indv address, city, state, and zip into their respective column? For example the query I run reports back the full address which is found in cell (A2). I would like to extracte the data from (A2) into the respective cells (B2)Address only, (C2)City only, (D2)State only & (E2)Zip only. A B C D E 1 Store Address (query raw data report) Address City State Zip 2 123 Candycane Ln: Gumdrop FL: 33443 Any help is so greatly appriciated and would be a huge time saver for me. Thank you again. rkumaran on "Linking Google Finance stock data with Excel" http://chandoo.org/forums/topic/linking-google-finance-stock-data-with-excel#post-1184 Sat, 13 Mar 2010 08:35:22 +0000 rkumaran 1184@http://chandoo.org/forums/ Hi, can Google Finance content on stocks be linked with an excel worksheet.....such that the user can have an updated portfolio everytime the file is opened....thanks...Kumaran Lo Baan on "Looking for Testing Form Template" http://chandoo.org/forums/topic/looking-for-testing-form-template#post-1183 Fri, 12 Mar 2010 17:55:38 +0000 Lo Baan 1183@http://chandoo.org/forums/ I am looking for a template of a simple table that will be used for testing. Each cell will have dedicated answers. In testing, folks will fill in blank cells and then can hit a button that will provide number of correct answers. Also another button that can clear all entries to start test again. If no template, could anyone tell me what Excel formulas and functions would I need to build this? Thanks, Lo Baan coriolis on "Date reminders/alerts" http://chandoo.org/forums/topic/date-remindersalerts#post-1164 Thu, 11 Mar 2010 16:48:30 +0000 coriolis 1164@http://chandoo.org/forums/ Hi, Im working on a spreadsheet for my employer. Its a record of all his quotes and sales. He has a column where he records the date he last contacted each client and want excel to change the colour of the cell after a set amount of time if he hasn't updated that cell. For example, if he enters 10/03/10, on the 17/03/10 he would like that cell to change colour to remind him that he needs to take action. He would then enter 17/03/10 and 7 days later would be reminded again. Any help much appreciated ! Thanks jcalvacca on "Credit Book Project" http://chandoo.org/forums/topic/credit-book-project#post-1171 Fri, 12 Mar 2010 09:54:46 +0000 jcalvacca 1171@http://chandoo.org/forums/ Ok here is the file, hopefully this will show the interface requested. Couple of things, I understand now (thanks to you guys) how the ISBLANK() function and how to return the "last" number of the list using the VLOOKUP() so I know how to clean up the ledger balance on each individual customer and return it to the zzzzSummaryzzzzz page. The problem I am experiencing is being able to create new customers and delete old ones as this list will grow and expand and have it return the necessary information to the zzzzSummaryzzzzz page. Now its a major cut/paste job having to create new references. Individual details on what they have purchased or added to their accounts is necessary to keep and the zzzzzSummaryzzzzz page is printed out daily to have as referrence and to log new tranactions on our counter, etc etc. <a href="http://www.4shared.com/file/239587759/1d8a7209/2009_Credit_Book.html" rel="nofollow">http://www.4shared.com/file/239587759/1d8a7209/2009_Credit_Book.html</a> Thanks in advance as always! Texan on "Merging Excel data" http://chandoo.org/forums/topic/merging-excel-data#post-1167 Fri, 12 Mar 2010 07:57:25 +0000 Texan 1167@http://chandoo.org/forums/ Hi, Want to use excel for staff to rate themselves. Each staff receives s/sheet, complete and mail back. How can I get the final rating score of each member (with their details) to one spreadsheet? Trust this is not a stupid (I am not good in excel).. Texan on "Nice forum" http://chandoo.org/forums/topic/nice-forum#post-1168 Fri, 12 Mar 2010 08:36:20 +0000 Texan 1168@http://chandoo.org/forums/ Hi, I came across PHD when I googled about an issue in Excel. I am quite impressed. Am not much good in excel, say average, but will certainly gain from this forum. Thanks and keep it up. Rgds Texan jcalvacca on "Using the INDIRECT function on multiple spreadsheets" http://chandoo.org/forums/topic/using-the-indirect-function-on-multiple-spreadsheets#post-1153 Wed, 10 Mar 2010 16:19:44 +0000 jcalvacca 1153@http://chandoo.org/forums/ Sheet1 has categories A B C with subsets D E F respectively (ie. Fruit makes list apple, orange, and starberry/Vegetable makes list onion, potatoe,) On Sheet2 I want to reference subgroups fruit and vegetable which are located on sheet1 What does the "source" area look like for that to happen I understand interface if its on same tab, but not if interface spans multiple tabs. Thanks in advance. AlinVegas on "RSS Feed list in Excel to Outlook" http://chandoo.org/forums/topic/rss-feed-list-in-excel-to-outlook#post-1156 Wed, 10 Mar 2010 18:20:08 +0000 AlinVegas 1156@http://chandoo.org/forums/ This should be simple but so am I... I have a list of RSS feed addresses in Excel 2007 that I want to import into Outlook Same. The import option for RSS is requiring OPML file. Is there a way I can save export or modify this simple list of addresses: <a href="http://rssaddress" rel="nofollow">http://rssaddress</a> to an OPML. -OR- A simple outlook macro that would read down the list and add the feeds? AK on "Unique values from an unsorted column meeting a certain criteria" http://chandoo.org/forums/topic/unique-values-from-an-unsorted-column-meeting-a-certain-criteria#post-1147 Tue, 09 Mar 2010 12:11:54 +0000 AK 1147@http://chandoo.org/forums/ Hello I need to derive sorted unique values from column. For the purpose I use the following formula from cell A3 below onwards entered as an array formula: INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(A$2:$A2, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0)) This formula gives me the desired results. However, now i want to derive sorted unique values which contain a string e.g. ABC If the column values are: ABC-001 DEF-001 GHI-003 ABC-001 ABC-002 GHI-008 I want to derive only the values: ABC-001 ABC-002 Could anyone please help? Thanks Gamnis on "Get user Signatur" http://chandoo.org/forums/topic/get-user-signatur#post-1143 Mon, 08 Mar 2010 11:27:46 +0000 Gamnis 1143@http://chandoo.org/forums/ Hi Right now i'm trying to find a way to copy the name or signature from the user thats logged in to the computer I have seen this work but I have had some trubble finding an example of it. So I hope some on of you guys can help me? EX: The user fills out my form and then presses a button (VB-skript) that transfers the data to another sheet or a database. This is were I want to logg the one who is inserting the data. data 1/data 2/.../.../[Initial of user] All comments and tips are welcome! Best Regards -- Gamnis (I'm Swedish so I hope you will excuse spelling errors and funny sentences) LadyBlack on "Updating links" http://chandoo.org/forums/topic/updating-links#post-1103 Tue, 02 Mar 2010 11:41:44 +0000 LadyBlack 1103@http://chandoo.org/forums/ Hello all I am doing another chart! I've just discovered that what I'm actually doing is an Excel dashboard, which I will be taking a course for next month. However, I'm trying to do some for my boss's next meeting this month, and am stuck. I am trying to link one worksheet to another. I have a chart to show the various bits of work being done over the month, and this is linked to the forecast chart (obviously, work done = income which then goes on the forecast). I've managed to get the Forecast sheet to show how much of a certain type of work is done every month, and I want to put the same information on the new Monthly chart. The path from the Monthly Report to the Forecast is fine, however, anytime the Forecast sheet is closed, and I do anything at all to the Monthly report, all my figures vanish and I get #VALUE! in my cells. Of course, my graph then disappears too. The links are set to update automatically, 'update remote references' is ticked and I get asked whether I want to update or not anyway. Why does it therefore NOT update, and will only do so when Forecast is open? Or am I trying to get Excel to do too much, and it WILL only update when Forecast is open at the same time? Many thanks in advance Gamnis on "To get correct time format with incorrect value??" http://chandoo.org/forums/topic/to-get-correct-time-format-with-incorrect-value#post-1100 Tue, 02 Mar 2010 09:19:56 +0000 Gamnis 1100@http://chandoo.org/forums/ Hi My name is Richard and I'm new to your webbsite. I'm working on an excell sheet that is going to collect information from different people with differrent knowledge in excel. Basically most of them does not know the right format for example time. To get the right time format you need to wright ex 10:12 It's common for many of the users to wright either 1012 10.12 10 12 or 10,12 Right now I have solved it by making the cellformat to time hh:mm so if the user wrights 1012 the outcome is 00:00 so it forces them to wright it right. Witch makes many of them quite angry... So my question is if there is some kind of VB code or format that automaticly rewrights the format to hh:mm Thankfull for any sugestion Best regards -- Richard (I'm from sweden so sorry for any bad english or if I translate anything wrong from my excel copy) gopinath1 on "picture links" http://chandoo.org/forums/topic/picture-links#post-1133 Sat, 06 Mar 2010 00:07:50 +0000 gopinath1 1133@http://chandoo.org/forums/ I have 10 pictures to be chosen to complete a form in a work sheet; Is there a way to drop list and choose pictures from the list of 10; i am a beginner and would like to avoid VBA.. any help appreciated suresh.79 on "SUM dynamically till a condition is reached?" http://chandoo.org/forums/topic/sum-dynamically-till-a-condition-is-reached#post-1136 Sun, 07 Mar 2010 07:18:43 +0000 suresh.79 1136@http://chandoo.org/forums/ hi i want to dynamically SUM cells till a certain condition is reached. Cell A1 has a value y Cell B1 has value x1, B2 has value x2, B3 has value x3, B4 has value x4 and so on I want to write a formula in cell C1 which will keep on summing x1, x2, x3 etc till the sum value is just greater than or equal to y eg A1 = 100, B1 = 50, B2 = 49, B3 = 5, B4 = 10, B5 = 41 ... so Cell C1 should give me the sum as (50+49+5) = 104 (ie keep on summing until the sum just becomes greater than or equal to 100) AMG on "Forecasting with Pivottables is it possible?" http://chandoo.org/forums/topic/forecasting-with-pivottables-is-it-possible#post-1132 Fri, 05 Mar 2010 12:30:33 +0000 AMG 1132@http://chandoo.org/forums/ I am responsible for maintaining a spreadsheet with all our employees. I record name, position, start and enddate in position amongst other things. Row 4 Name (A), dept (B), position (C), headcount (D), start (E), end (F), reportmonth (G) Row 5 Tom, dept a, manager, 1, 1-1-2008,31-12-9999,true Row 6 Dick, dept b, analyst, 1, 1-1-2008,31-12-9999,true Etc.... Row 999 Harry, dept c, adviser, 1, 1-1-2008,31-12-9999,true On a monthly basis I need to report how many active employees we have. All active employees will have their enddate set to 31-12-9999 If Harry leaves at end of march I will amend the enddate to 31-3-2010 Report date (in march) in cell A1 is a named cell and will contain 01-Mar-2010. Column G (report month) contains a formula IF(AND($E2<=ReportDate,$F2>ReportDate),TRUE,FALSE) The result of this formula will be true if the employee is still in his position in the reporting month. I use a pivot table to generate results Filter: Reportmonth = True Rows: Departments Sum: Headcount (HC) The result of total will be 3 (Tom, Dick and Harry) If I change the report date to 1 Apr 2010 the result will be 2 (harry left) So far so good. Now I also need to do forecasting: I’d like to be able to produce what the headcounts would be for future months (e.g. till end of 2010) by setting up 12 pivot tables, one for each reporting month and by amending start and enddates of employees for those where Ive been given the correct dates. E.g. I know now that Dick will leave in June and I know that we will hire 2 resources in May (Joe and Jane) So the totals of my pivot tables should show Jan 2010 3 HC Feb 2010 3 HC Mar 2010 3 HC Apr 2010 2 (harry left) May 2010 2 + 2 = 4HC (2 hires Joe and Jane) Jun 2010 4 HC Jul 2010 4 - 1 = 3 HC (Dick left) Aug 2010 3 HC Sep 2010 3 HC Etc Is it possible to create these forecasting pivots based on the above data? If so how... I hope I've explained this well enough Dee on "Selecting multiple options from data validation list" http://chandoo.org/forums/topic/selecting-multiple-options-from-data-validation-list#post-1129 Fri, 05 Mar 2010 06:23:33 +0000 Dee 1129@http://chandoo.org/forums/ Hi All, Can anybody help me on this? I need to select 2-3 options from the pull down menu list and it should appear in single cell separated by comma. Is there a way to get it without using VBA? Thanks, Dee