Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums: Recent Topics http://chandoo.org/forums/ Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Thu, 17 May 2012 01:18:35 +0000 http://bbpress.org/?v=1.0.1 <![CDATA[Search]]> q http://chandoo.org/forums/search.php bard on "cant get KEI formula B2^2/(C2) to work" http://chandoo.org/forums/topic/cant-get-kei-formula-b22c2-to-work#post-26089 Thu, 17 May 2012 00:13:11 +0000 bard 26089@http://chandoo.org/forums/ Hi I haven't used excel much before. All I kept getting was a zero in the cell D2 and then D3 where answer should appear as I selected those cells. I got this formula to work in one of the D columns where the result is meant to appear but only after messing about and trying to put the formula in the formula box and also in the cell where I wanted the answer to appear - now I keep getting zeros. I am not sure how to make this work but it seems there is nothing intuitive about using Excel. Any help please because the amount of time I have spent I could have calculated the answers and written them in by hand quicker. Thanks sonya stone on "Need HELP w. Project Mgmt Dashboard" http://chandoo.org/forums/topic/need-help-w-project-mgmt-dashboard#post-26091 Thu, 17 May 2012 00:47:49 +0000 sonya stone 26091@http://chandoo.org/forums/ Hi All! I am new to Chandoo -- and would greatly appreciate a bit of help. I got the Proj Mgmt Dashboard, which I think is great(!), and have customized it. I'm decent on Excel, but not a Charting Wizard (yet!).. My Gantt Chart Template Tab now has 50 lines I am tracking -- I would like to see them pull over to the Dashboard, but only 30 lines are showing (even though there is room to extend.) How do I extend the chart down on the Dashboard?? Whenever I try to click on it, or grab the edge to pull down, it just clicks me over to the Gantt Chart Tab...! Also, how do I grab the Burndown Chart for example, and move it down on the page, in order to make room?? -- Stumped! max4asd on "Help in Formula" http://chandoo.org/forums/topic/help-in-formula#post-25812 Mon, 14 May 2012 13:03:35 +0000 max4asd 25812@http://chandoo.org/forums/ Hi Everyone, I am working on a Dash board and have created 4 different list values and i want to display the data in the table below to show the numbers based on my selection criteria in each list. Right now i am using IF condition with SUMIF function but its working fine with me for only 2 list values if i wnat to filter the info based on the other list values what should be the formula. The below formula is able to filter only 2 list values if i want to add 2 more filter how can i do it. I appriciate any help provided and thanks in advance. Max =IF(J3="All",SUMIFS(Sheet1!$H$3:$H$758,Sheet1!$A$3:$A$758,'Dash Board1'!$C$3,Sheet1!$E$3:$E$758,'Dash Board1'!$G$3,Sheet1!$D$3:$D$758,'Dash Board1'!C$5,Sheet1!$F$3:$F$758,$J$3,Sheet1!$F$3:$F$758,$J$3),SUMIFS(Sheet1!$H$3:$H$758,Sheet1!$A$3:$A$758,'Dash Board1'!$C$3,Sheet1!$E$3:$E$758,'Dash Board1'!$G$3,Sheet1!$D$3:$D$758,'Dash Board1'!C$5,Sheet1!$G$3:$G$758,$J$3)) xljgd on "vlookup for dynamic columns based on multiple data selections" http://chandoo.org/forums/topic/vlookup-for-dynamic-columns-based-on-multiple-data-selections#post-26045 Wed, 16 May 2012 13:22:57 +0000 xljgd 26045@http://chandoo.org/forums/ Hello All, I have 2 value that i select using a pull down. The number and the month. At each row if i select say no 1 and month May i should lookup the corresponding value. I have used vlookup to get values from the table for all other columns but not able to solve the value for the month column because it is based on 2 multiselect option No and Month. Eg. <pre><code>No Jan Feb Mar April 1 test prepare inspect ship 2 ship test test inspect 3 prepare test prepare ship</code></pre> if the above is my table. <pre><code>Select a month The month and no are from a drop down list. No Jan 1 test 3 prepare 2 ship</code></pre> Next time when i change the month to Mar I should get <pre><code>Mar 3 prepare 1 inspect 2 test</code></pre> I have tried, match, index , vlookup but the problem is this column cannot be hardcoded. can anyone help. sorry about the confusion on posting earlier on the forum were i was not supposed to. thanks Kamarlon on "Using Access to collect data for excel dashboard" http://chandoo.org/forums/topic/using-access-to-collect-data-for-excel-dashboard#post-26053 Wed, 16 May 2012 14:42:45 +0000 Kamarlon 26053@http://chandoo.org/forums/ I would like to express a big thank you to Dan who guided me in setting up an Access database. The database is stored on a shared drive. I created a query that collects the data from various tables and I then export the data to the input worksheet in the file containing the dashboard. Access allows 2 big bemefits: - you can create a log in form where users select their names and enter a password. Based on who the user is they will get access to only the forms which they are supposed to see - data integrity. In the database i created after persons update the monthly information, a manager goes through to ensure the data is correct. He makes any necessary correcions and and then selects "closed" from a dropdown box. When he selects "closed", users are not able to make changes to the past data entered. I highly recommend using Access to have multiple users enter their data and then exporting this data to excel to update the dashboard. Thanks again Dan. southwoman on "Using mach if or vlookup in excel" http://chandoo.org/forums/topic/using-mach-if-or-vlookup-in-excel#post-26080 Wed, 16 May 2012 19:39:44 +0000 southwoman 26080@http://chandoo.org/forums/ Hello, am having some issues representing some regional data in my charts. I have 4 regions with each having 3 - 5 sub regions and relevant data. What I am seeking to do is create a drop down list that matches the regions to its sub region with the data set included. For example: Region 1 SBR1, 456, 800 SBR1a, 234,400 SBR1b; 200, 300 Region 2 has SBR2, 900,600 SBR2a, 350, 250 SBR2b 256, 890 Region 3 has SBR3, 987, 654 SBR3a, 345, 780 SBR3b 300, 500 How can i do a drop down list that only includes and SHOWS the subregion and its data for that particular region only. help anyone? anujable on "MOVING DATA FROM ONE SHEET TO ANOTHER IN THE SAME EXCEL FILE WHEN CONDITION MET" http://chandoo.org/forums/topic/moving-data-from-one-sheet-to-another-in-the-same-excel-file-when-condition-met#post-26030 Wed, 16 May 2012 10:47:06 +0000 anujable 26030@http://chandoo.org/forums/ I have an excel file with multiple sheets and I'm tracking sale and payment received. Please suggest a way to move the entire row of data when the payment is received from the client. and then move the data from this sheet to another when i receive my commission from the company realtop17 on "vlookup" http://chandoo.org/forums/topic/vlookup-6#post-26070 Wed, 16 May 2012 18:35:37 +0000 realtop17 26070@http://chandoo.org/forums/ using vlookup how can i get the latest date if the table array have the same or duplicate number EXAMPLE. <pre><code>Serial Part no. contorl # date R20120404 P04-060252-00311-E000 ctrl 10 4/1/2012 R20120484 P04-060160-00111-6000 ctrl 10 4/1/2012 21524118826CC5201958 P04-181560-10311-C000 ctrl 10 4/1/2012 21524118826CC5202078 P04-181560-10311-C000 ctrl 10 4/1/2012 21524131476CC5201761 P04-060340-10111-C000 ctrl 10 4/1/2012 R20120404 P04-060252-00311-E000 ctrl 25 5/17/2012 R20120484 P04-060160-00111-6000 ctrl 25 5/17/2012 21524118826CC5201958 P04-181560-10311-C000 ctrl 25 5/17/2012 21524118826CC5202078 P04-181560-10311-C000 ctrl 25 5/17/2012 21524116506CC5202301 P04-070154-10211-C100 ctrl 25 5/17/2012</code></pre> IKHAN on "Looping issue excel" http://chandoo.org/forums/topic/looping-issue-excel#post-26071 Wed, 16 May 2012 18:39:20 +0000 IKHAN 26071@http://chandoo.org/forums/ Hello,Need help!!! with excel Macro to pull data from third party application and email the body to outlook if any data present ,below macro does it however need some help Tweakin it. ISSUE : Below MACRO goes in a loop after displaying results in excel sheets and not able to call/send email unless i run email macro (Sub Mail_Selection_Range_Outlook_Body())separately. -lookin for macro to run every 30 minutes and email the body, if query is empty no email. <pre><code>Private Sub Workbook_Open() ' This procedure clears the username and password fields ' on the login worksheet when the workbook is opened. Worksheets("Login").Cells(3, 2) = "" Worksheets("Login").Cells(4, 2) = "" Worksheets("Login").Activate Range("B3").Activate End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) ' This procedure clears the username and password fields ' on the login worksheet when the workbook is closed. Worksheets("Login").Cells(3, 2) = "" Worksheets("Login").Cells(4, 2) = "" End Sub Public Sub Run_Query() ' This procedure reads the query from the active worksheet ' and passes the string to the RetrieveRecordSet function. ' The two variables passed to the RetrieveRecordSet function ' are the query string and the destination cell of where to ' display the resulting query, in this case, cell A10. Dim sSQL As String Dim check Dim RowCount As Long Dim Incident As String Dim IncidentDate As Date check = True RowCount = 10 sSQL = Cells(3, 2) Do ' Pull the incidents from Remedy Call RetrieveRecordSet(sSQL, [A10]) ' ActiveWorkbook.PrecisionAsDisplayed = False ' Now check the duration for each incident RowCount = 10 Incident = Cells(RowCount, 1) IncidentDate = Cells(RowCount, 4) While Incident > "" ' Calculate the duration in minutes If DateDiff("n", IncidentDate, Now) > 60 Then 'ActiveWorkbook.SendMail Recipients:="xxx" End If RowCount = RowCount + 1 Incident = Cells(RowCount, 1) IncidentDate = Cells(RowCount, 4) Wend Application.Wait (Now + TimeValue("0:00:10")) ' Clear all the incidents Range("A10:AZ50000").Select Selection.ClearContents Loop Until check = False End Sub Public Sub RetrieveRecordSet(strSQL As String, clTrgt As Range) Dim Cn As ADODB.Connection Dim Rs As ADODB.Recordset Dim dbConn As String Dim uName As String Dim uPassword As String ' Clear the contents of the destination cells Range("A10:az50000").Select Selection.ClearContents ' The username and password are read from the login worksheet and ' stored in the uName and uPassword fields. uName = Worksheets("Login").Cells(3, 2) uPassword = Worksheets("Login").Cells(4, 2) ' The following routine checks if the Remedy username and/or password ' variables are blank. If they are, a message is displayed and ' the user must enter the missing information. If (uName = "" Or uPassword = "") Then Worksheets("Login").Activate If (uName = "" And uPassword = "") Then MsgBox ("The Remedy username and password fields are blank. Please confirm your login details.") Worksheets("Login").Range("B3").Activate Exit Sub End If If uName = "" Then MsgBox ("The Remedy username field is blank. Please enter your username.") Worksheets("Login").Range("B3").Activate Exit Sub End If If uPassword = "" Then MsgBox ("The Remedy password field is blank. Please enter your password.") Worksheets("Login").Range("B4").Activate Exit Sub End If Exit Sub End If ' Set database source. The connection string is stored in the string dbConn. dbConn = "DSN=AR System ODBC Data Source;ARServer=xxxxxxxx ;ARServerPort=xxxx;UID=" + uName + ";PWD=" + uPassword + ";ARAuthentication=;SERVER=NotTheServer" 'Create RecordSet Set Cn = New ADODB.Connection Set Rs = New ADODB.Recordset Cn.ConnectionString = dbConn Cn.Open ' The following establishes the connection to the Remedy datasource and ' and passes the resulting dataset back to ht RS recordset. With Rs Rs.ActiveConnection = Cn Rs.Open strSQL If (Rs.EOF) Then ' MsgBox ("Query is empty") Else clTrgt.CopyFromRecordset Rs ActiveSheet.Calculate 'ActiveWorkbook.Worksheets("Sample Query").Range(clTrgt).CopyFromRecordset Rs End If Rs.Close End With On Error Resume Next If Err.Number <> 0 Then GoTo EarlyExit EarlyExit: ' This closes/clears all the variables. Rs.Close Cn.Close Set clTrgt = Nothing Set Cn = Nothing Set Rs = Nothing End Sub Public Sub Run_AllQuery() ' This procedure scans the support worksheet for all the worksheet names ' and runs the RetrieveRecordSet function for every worksheet listed. Dim RowCount As Long Dim sSQL As String Dim WSName As String ' Turn off screen updating and set auto calculation to manual. Application.ScreenUpdating = False Application.Calculation = xlManual WSName = "" Worksheets("Support").Activate ' This sets the starting row number for the worksheets. RowCount = Cells(2, 2) While Cells(RowCount, 2) > "" On Error Resume Next WSName = Cells(RowCount, 2) Worksheets(WSName).Activate sSQL = Cells(3, 2) Call RetrieveRecordSet(sSQL, [A10]) sSQL = "" WSName = "" Worksheets("Support").Activate RowCount = RowCount + 1 Wend ' Turn on screen updating, activate the login worksheet, turn on auto calculation, and ' force a recalculation of the workbook. Application.ScreenUpdating = True Worksheets("Login").Activate Application.Calculation = xlAutomatic Calculate End Sub Sub Mail_Selection_Range_Outlook_Body() Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeVisible) Set rng = Sheets("Incident Scanning").Range("A9:A15", "L9:L15").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = "xxx@yahoo.ca" .CC = "" .BCC = "" .Subject = "Test email from Excel" .HTMLBody = RangetoHTML(rng) .Send 'or use .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function</code></pre> brijendraydv on "Create graph based on checked check boxes(filter criteria)" http://chandoo.org/forums/topic/create-graph-based-on-checked-check-boxesfilter-criteria#post-26074 Wed, 16 May 2012 18:53:17 +0000 brijendraydv 26074@http://chandoo.org/forums/ Hello Experts, Please help me on this i have got this requirement very urgent.I have attached my file on this link.my requirement is explained in this link: <a href="http://speedy.sh/f4cVt/requirement.xls" rel="nofollow">http://speedy.sh/f4cVt/requirement.xls</a> and my raw data is in this link: <a href="http://speedy.sh/Sstej/AUT-TEBIT-FY11-13.xls" rel="nofollow">http://speedy.sh/Sstej/AUT-TEBIT-FY11-13.xls</a> Region,commodities,status data is there in raw data file highlighted in yellow columns.I want if for example-region ASIA is selected in check box then when i select any commodity from the checkbox then when i click any status the corresponding graph of how many project(project ids in raw data,first column) are approved,active/implement,potential etc for that commodity and region should come out. Please help me on this.let me know for any queries. Thanks and Regards, Montrey on "Diablo 3 tomorrow!" http://chandoo.org/forums/topic/diablo-3-tomorrow#post-25830 Mon, 14 May 2012 15:29:49 +0000 Montrey 25830@http://chandoo.org/forums/ Anyone play PC games? Diablo 3 comes out tomorrow! So excited! If you have never played it and like PC games. BUY IT. realtop17 on "inventory accuracy" http://chandoo.org/forums/topic/inventory-accuracy#post-26054 Wed, 16 May 2012 14:53:05 +0000 realtop17 26054@http://chandoo.org/forums/ weekly inventory are our weekly task using scsanner we do manual scanned over 7,000 serials we scanned serial per location see below exsample. how can we monitor the incomning units and out going units using excel. example: <pre><code>serial # p/n location # 21524131066CBA102066 P04-281008-00111-C000 R015-A4 21524131066CB1004102 P04-281008-00111-C000 R015-B4 21524118596CBCA00070 CWH-231200-00711-C000 R015-C4 21524113326CB6001343 P02-181010-10211-C000 R015-D4 21524118696CC5202233 P04-181010-00111-C000 R015-E4 21524115736CB9104317 P04-261008-10111-C000 R015-F4 21524118656CC3206775 P04-231232-00311-C000 R015-F3 21524113446CC2102154 P02-381260-10111-C100 R015-E3 21524118816CC5202177 P04-181560-00311-C000 R015-D3 21524115736CB9104040 P04-261008-10111-C000 R015-C3 21524118606CBCA00023 CWH-231200-10711-C000 R015-B3 21524115736CB9103755 P04-261008-10111-C000 R015-A3 21524131446CC4203270 P04-060252-00311-C000 R015-A2 21524113326CB6000643 P02-181010-10211-C000 R015-B2 21524115766CB9103006 P04-261008-00111-C000 R015-C2 21524131066CBA102453 P04-281008-00111-C000 R015-D2 21524115736CB9105042 P04-261008-10111-C000 R015-F2 21524115766CB9104012 P04-261008-00111-C000 R015-F1 21524101806CC5202142 P02-231008-10311-C000 R015-E1 21524113326CB6000714 P02-181010-10211-C000 R015-D1 21524131066CB1003440 P04-281008-00111-C000 R015-C1 21524118816CC5202179 P04-181560-00311-C000 R015-B1 21524113326CB6000669 P02-181010-10211-C000 R015-A1</code></pre> sakshi_sak on "Automatically copy data from multiple sheets into one summary sheet" http://chandoo.org/forums/topic/automatically-copy-data-from-multiple-sheets-into-one-summary-sheet#post-25879 Tue, 15 May 2012 09:16:42 +0000 sakshi_sak 25879@http://chandoo.org/forums/ Hi, I am new to excel and i am having a challenge now. I have a project spreadsheet which has one main summary sheet and n no.of sheets for individual projects. What i want is, 1. when a project sheet is added, when common items such as project name, domain,loc etc are populated, i want the same information to be updated in the summary sheet as 1 row for each project. 2. The summary sheet data organized columns wise, whereas the individual project sheets have data organized row wise. 3. Everything is in one workbook I am not sure how to accomplish this, can any one help?? Vishu Monga on "Format for Stock Shrinkage (shoratge)" http://chandoo.org/forums/topic/format-for-stock-shrinkage-shoratge#post-26062 Wed, 16 May 2012 16:27:26 +0000 Vishu Monga 26062@http://chandoo.org/forums/ Can u plz provide a format for stock which is found short at the time of stock take. tarun_1 on "Lookup query" http://chandoo.org/forums/topic/lookup-query#post-26026 Wed, 16 May 2012 09:41:57 +0000 tarun_1 26026@http://chandoo.org/forums/ Hi, I have got a query in excel lookup. (i wish here an option should be there to upload file for better clarity) i need to lookup data with database and i need buckets for it. eg: <pre><code>Database: India 1 3 12 30 50 100 500 1000 Jordan 1 3 12 30 50 100 500 1000 UAE 1 3 12 30 50 100 500 1000 now: India 1 Jordan 2 UAE 3</code></pre> should give: 1 to 3 for each country because it falls between 1 and 3 Similarly if we enter 10 it should give 3 to 12. Data is huge and weekly. My friend has given a complicated IF and Vlookup formula but i think there should be a better way. Also - bucketing should be dynamic w.r.t. database i.e. if we change Jordan - their bucketing should automatically change - Eg. <pre><code>Revised Database: India 1 3 12 30 50 100 500 1000 Jordan 0 4 12 30 50 100 500 1000 UAE 1 3 12 30 50 100 500 1000 now: India 1 Jordan 2 UAE 3</code></pre> should give: India 1 to 3; Jordan 0 to 4; UAE 1 to 3 in each corresponding cells of respective country. Deepa on "Excel protection for users" http://chandoo.org/forums/topic/excel-protection-for-users#post-26055 Wed, 16 May 2012 14:57:09 +0000 Deepa 26055@http://chandoo.org/forums/ Hello, My requirement is to give edit permission for individual user to particular excel sheet. All other user can only view the sheet and all the sheets contains grouping. Kindly help in resolving this. Thanks in advance Regards, Deepa reachkiran03 on "prefix 91 before mobile numbers" http://chandoo.org/forums/topic/prefix-91-before-mobile-numbers#post-26041 Wed, 16 May 2012 13:11:40 +0000 reachkiran03 26041@http://chandoo.org/forums/ Sir, I have stored mobile numbers as 9980012780 like this i have stored around 500. Now i would like to prefix 91 before all the mobile numbers tat i have stored in Excel at one go how to do it at one go. Please help me by giving some suggestions. Thanks in advance Kiran Injinia on "Macro to continously copy and paste without erasing previous data" http://chandoo.org/forums/topic/macro-to-continously-copy-and-paste-without-erasing-previous-data#post-26035 Wed, 16 May 2012 12:16:53 +0000 Injinia 26035@http://chandoo.org/forums/ Hi The macro below copies the first round of values, then on running it again it copies over the previously copied values. How do I get it to continuously copy the values & number formats to the next blank cell on column A in sheet 1, instead of copying over already existing data -Injinia Sub TransferData() Dim LastRow As Integer 'Where is the last cell with data? LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row 'Transfer data Sheets("accuracy").Range("B23:L29").Copy Worksheets("Sheet 1").Cells(LastRow + 1, "A").PasteSpecial xlPasteValuesAndNumberFormats End Sub Injinia on "Macro to Copy and Paste from one sheet to another sheet" http://chandoo.org/forums/topic/macro-to-copy-and-paste-from-one-sheet-to-another-sheet#post-23556 Thu, 19 Apr 2012 08:55:35 +0000 Injinia 23556@http://chandoo.org/forums/ I would like help to write a macro that copys data and pastes it on a new sheet always on the next empty row. I have this but it keeps on giving me an error @ " Set emptyCells = Range("A:A").SpecialCells(xlCellTypeBlanks)" Sheets("accuracy").Select Range("B23:L29").Select Selection.Copy Sheets("Sheet 1").Select Set emptyCells = Range("A:A").SpecialCells(xlCellTypeBlanks) Range(emptyCells.Cells(1).Address, emptyCells.Cells(1).Offset(7, 10).Address).Select ActiveSheet.Paste Range("C23").Select Sheets("accuracy").Select MarkH on "the Logic of: =SUMPRODUCT((G2:G89&"")/COUNTIF(G2:G89;G2:G89&"")) and how" http://chandoo.org/forums/topic/the-logic-of-sumproductg2g89countifg2g89g2g89-and-how-else#post-25933 Tue, 15 May 2012 16:38:33 +0000 MarkH 25933@http://chandoo.org/forums/ Hi, The above works to get me the distinct number of values from a list, but I don't understand why. Some help would be nice. Maybe partuially to me not understanding it, I cannot "customize" this one to suit other needs. How can this be done (so I understand) using the CountIF formula and anything else you need. The logic to just hop from array and criteria to array and criteria seems appealing. If I also get why the array and why not, I am all set. Thanks. MarkH on "Counting distinct values in an array, under a condition" http://chandoo.org/forums/topic/counting-distinct-values-in-an-array-under-a-condition#post-25932 Tue, 15 May 2012 16:29:43 +0000 MarkH 25932@http://chandoo.org/forums/ Hi, Two questions, but they are related: First; the problem: I want to count the number of distinct values in a list of names. In another column however the name has a "status". This status will change in the course of time (ie will be updated). The status is a value from a value data list. I want the number of distinct values under the condition that the name does NOT have a certain status. The total number of names to go through is several hundreds. Second: Understand to reproduce and add knowledge I am really interested in understanding the solution, so no blind cc of the formula and finished. This is my way of "getting" the logic that helps me to next time do it myself. Thanks!! Injinia on "copy cells with formulas and paste values" http://chandoo.org/forums/topic/copy-cells-with-formulas-and-paste-values#post-25892 Tue, 15 May 2012 11:02:56 +0000 Injinia 25892@http://chandoo.org/forums/ Hi, How could I use the below macro to copy cells with formulas and paste them as values? Sub TransferData() Dim LastRow As Integer 'Where is the last cell with data? LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row 'Transfer data Sheets("accuracy").Range("B23:L29").Copy Worksheets("Sheet 1").Cells(LastRow + 1, "A") End Sub Foxy17 on "Sorting with Macro -Furmula dose not reflect previous page and mixes up results" http://chandoo.org/forums/topic/sorting-with-macro-furmula-dose-not-reflect-previous-page-and-mixes-up-results#post-25818 Mon, 14 May 2012 13:57:07 +0000 Foxy17 25818@http://chandoo.org/forums/ Macros Created for all colums do be sorted , how ever they all work on Voyage A , but mix up the ansers on Voyage B and C, so I recon it's the frmula that being problematic and not the macro. I admit I have posted this recently butI've been assured that my macros are ok, so I can see the solution to the problem. A final call for some help if possible. thanks <a href="http://speedy.sh/Ssc4j/Small-File.xlsm" rel="nofollow">http://speedy.sh/Ssc4j/Small-File.xlsm</a> SirJB7 on "Excel multilanguage formula translator and function reference" http://chandoo.org/forums/topic/excel-multilanguage-formula-translator-and-function-reference#post-25986 Tue, 15 May 2012 21:21:26 +0000 SirJB7 25986@http://chandoo.org/forums/ Hi! I think I'm done with the finishing touches to a project related to functions and formula translations between ... a lot of languages. As you well know I use a Spanish version of Excel and every time I post a formula I feel and seem obliged to traduce it to English so as to not overcharge users who asked -may times elementary things- with the additional issue to translating from a language that they might even ignore. I used to use many links like: <a href="http://www.piuha.fi/excel-function-name-translation/index.php?page=espanol-english.html" rel="nofollow">http://www.piuha.fi/excel-function-name-translation/index.php?page=espanol-english.html</a> <a href="http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html" rel="nofollow">http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html</a> among the bests I could find, but there was always a manually search, type, research, retype... As I recognize me lazy (I'd say absolutely efficient about unnecessary repetitive and automatable tasks), I began wondering "WTF, I can't find a f... translator!" (... and I couldn't!) -F&f stands for fabulous ;)- and I ended thinking "Why not building one?"... And I'm done, I finished it a couple of months ago and it was firstly intended for personal use, it just did the job, but then I thought "This might be useful for some other guys, I can share it with them", so it evolved a little. The information and files at the websites I mentioned appeared to have many faults or incompleteness or lacks: or they were only english-spanish, or they haven't all the functions, or they were as user friendly as porcupine mating, or... always there were ors. In my search thru the very main source of all this function names (Microsoft, obviously) I found this pages: <a href="http://office.microsoft.com/es-es/excel-help/excel-functions-by-category-HP010342656.aspx" rel="nofollow">http://office.microsoft.com/es-es/excel-help/excel-functions-by-category-HP010342656.aspx</a> <a href="http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP010342656.aspx" rel="nofollow">http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP010342656.aspx</a> Playing with this and with the language codes I collected an important data source. Actually there are 94 LIP or LP (language interface packs or language packs) for Office 2010, but with that page structure (replacing the language code page) I could only find 37. Which is far beyond the best site (the second) which had 16 languages, not all functions well translated and not all functions available (I think that not even those in 2007 full version). I said I was done... well, nearly, almost, but not 100% sure. Why? I included every language found, 37, most of them from countries with languages different from Spanish, English, French, Portuguese, Italian (which acceptably or not I use and can handle). Among them I want to point out at those called RTL (right to left) as Hebrew and Arabic that faced me with the handling of strange text strings that when edited moving the cursor from first character to last was unusual. And I don't mention when mixing strings of both normal (left to right) and RTL languages... that was and is absolutely confusing, at least for me. So here is my wish list, if someone can help I'd appreciate it too much: a) do you know fully operating formula translators? no matter between what languages b) do you know additional pages as MS's ones with other link structure URL? c) what languages do you fluently know (among those for which there are Excel versions) so as to check let's say 10 translated formulas? I'm very interested in: - non-latin languages (cyrillics, hindi, thai, etc.) - RTL right-to-left languages from Middle East (hebrew, arabic, etc.) - ideographic languages from Far East (chinese, japanese, korean, etc.) - languages that use other chars apart from comma ",", colon ":" and semicolon ";" for list separator, decimal point and digit separator d) would you be willing to check partially or fully a 37 by 4 word list? (words: function, type, description and version, in the 37 languages yet found) e) have you ever faced issues of mixing/concatenating characters in different languages? i.e., "???? / Hebrew" here it seems friendly, but look at column A of sheet "Languages", copy it to another workbook and try to select characters from left to right or from right to left... I wish to thank specially to Faseeh (at this Chandoo's website forums) for his assistance in testing and interchanging opinions about non-occidental languages. As well as I'll appreciate any comment, suggestion, correction or idea that you might provide, I wish to thank you in advance just for reading this, whether or not you may be able to do anything about it. Please feel absolutely free to be critical, skeptical or cold-blooded cruel with your feedback. Download link: <a href="http://dl.dropbox.com/u/60558749/MS%20Excel%202010%20International%20Multilingual%20Formula%20Translator%20V1.0.xlsm" rel="nofollow">http://dl.dropbox.com/u/60558749/MS%20Excel%202010%20International%20Multilingual%20Formula%20Translator%20V1.0.xlsm</a> Regards! ushzz on "Writing array into worksheet using macro" http://chandoo.org/forums/topic/writing-array-into-worksheet-using-macro#post-25880 Tue, 15 May 2012 09:26:08 +0000 ushzz 25880@http://chandoo.org/forums/ Hi, Is it possible to write a partial array into the worksheet range at once? To be more clear, i don't want to write the zero values in my array into the file. Loops are taking so long. Thanks in advance. vat123 on "Excel formula Doesn't work" http://chandoo.org/forums/topic/excel-formula-doesnt-work#post-26007 Wed, 16 May 2012 05:39:56 +0000 vat123 26007@http://chandoo.org/forums/ Hi I have got a formula mentioned below that doesn't work. First two parts of the formula works but not the last condition. Anyone could help please? =IF(K6="No","-",IF(OR(K6<H6),0%,IF(AND(K6>=J6),200%,IF(K6>=H6,(50%+(K6-H6)*10),IF(K6>=I6,(100%+(K6-I6)*20),Error ))))) MAYANK OJHA on "CONVERT NUMBER INTO TEXT" http://chandoo.org/forums/topic/convert-number-into-text#post-26003 Wed, 16 May 2012 04:24:25 +0000 MAYANK OJHA 26003@http://chandoo.org/forums/ HOW TO CONVERT A NUMBER SAY 30 IN TEXT THIRTY Learner on "Formula Help with reference to Date" http://chandoo.org/forums/topic/formula-help-with-reference-to-date#post-25998 Wed, 16 May 2012 03:33:21 +0000 Learner 25998@http://chandoo.org/forums/ Hello Everyone, Sorry for cross posting this somewhere else with no malice intentions. I have a question on making a formula with real time date function. I have built my own simple personal money management worksheet. I want to know how I can write a formula which can automatically change 1. update my monthly EMI under my monthly expenses column 2. update my cumulative mortgage amount from my total debt column, every month by the 15th for the next 5 years. I have this data for the month date, cumulative mortgage and the corresponding monthly EMI as an amortization table inside the sheet and now I am manually doing it every month which gets automatically added to the expenses and simultaneously gets adjusted from my Total Savings Debt etc. I tried lots of formulas mainly with IF but I cannot do it for more than a month. Gross Income: B4 Net Income: B9 Mortgage: E4 Net Expenses: E9 System Date : F1 (Now () ; in format : May 16, 2012) Amortization Table Date : A17: A 30 (12 months)(in the format: mm/dd/yy) Amortization Table EMI : B 17 : B 30 ( I put constant EMI as data but could be variable EMI) Amortization Table Cumulative Debt : C17: C30 Note: How can I add the Test Macro enabled excel 2007 worksheet to this post so its clear for everyone who would like to respond to what I mean and want the formula for ? Thanks in advance for your efforts on my behalf. Learner RM Don Corleone on "Count down clock" http://chandoo.org/forums/topic/count-down-clock#post-25997 Wed, 16 May 2012 03:01:40 +0000 Don Corleone 25997@http://chandoo.org/forums/ I want to build a graph showing our sales growth. I will update it qtrly but I want it to have a year ending count down clock so my sales team can see the time ticking away to reach our corporate goals. How do I put a Year Ending counting down clock in the spreadsheet or near the graph?? ml8 on "Comparing 4+ Lists of Data" http://chandoo.org/forums/topic/comparing-4-lists-of-data#post-25991 Tue, 15 May 2012 23:35:17 +0000 ml8 25991@http://chandoo.org/forums/ I thought I knew where to start with this but sadly I it's been long enough to where I need some assistance. First of all what I am working with is four lists of product data that I need to compare against my own product data. So for example each of the product lists contain the following data: Product Category Product Name Inventory Product Price Each of the lists have at least a thousand records. What I need is a fifth list that tells me which of the competitors products I don't have on my list. Last but not least and where this gets trickier is that the inventory contains duplicate records. Its hard for me even to construct this question as I spend time with it. What I want though is a list of products that are shared by all of the competitors that I do NOT have on my product list. Before I type anymore I am going to see if you're following me here. Thanks for your time. -Michael