• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. D

    Calculating Max date selected using Date Slicer

    Hi, I am creating a report which has one Date Slicer (a slider to change the start or end dates). If start date and end dates are changed by user to view the historical numbers (lets say to see numbers for previous quarter), how would I calculate (using DAX formula) that what date range user...
  2. D

    Need to populate Bullets and Sub-Bullets numbering

    Hi, We have around 100 workbooks. Each workbook has a table. Each table grid has 100s of cells. Every cell has some bulleted text. Sample is given in attached excel in "Input text" sheet (I gave only 1 cell here but there is a table of such cells). The problem is that these excels needs to be...
  3. D

    Help with Filter Function

    Hi, I have a dataset named "AllData". It has a column named as "ObjectID". I want a filtered table basis the criteria that values in ObjectID column contains a value string "SearchString". The formula syntax for filter function is FilteredDataTable =...
  4. D

    Query on (CountA with All function)

    Hi All, I was wrote following DAX formula to count distinct values present in a column. "=CountA(All(EmpTable[EmpName]))" but it gives error Then I tried "=Countrows(All(EmpTable[EmpName]))" and it worked. Can you make me understand why first formula didn't work? "ALL" function provided...
  5. D

    Unable to add PowerQuery data to DataModel in Excel2016

    Hi, I have my base data in MS Access. I connected database with Excel using PowerQuery and modified the data in a usable structure and then clicked on "Only Create Connection" check box and then selected "Add this data to the Data Model". Now in Power Pivot, I don't see the table there. Once I...
  6. D

    Help with DAX formula

    Hi All, I need help with calculating team wise number of users who are visible in at least 2 months out of last 3 months data. I have uploaded a sample file. Also a small desired result mentioned in same worksheet. Thanks, DJ
  7. D

    Connection Strings

    Hi All, Can someone help me get some simple examples on connection strings? also which library reference needs to be selected (if applicable) ? If there are online study material is available, please share that too. Thanks, DJ
  8. D

    Named Range question

    Hi All, I was trying to create a demo for my friend to help him understand creating dependent lists. While doing so I noticed a strange scenario which is I am mentioning below....please help me understand why this happens. I entered sub-category names in column-A...
  9. D

    Selecting multiple items from a pivot's page field

    Hi All, I am trying to select multiple items from a pivot table's page field. Below code is not working. When I record macro to do this....it only sets property as "False" for the not selected items...nothing for selected items...
  10. D

    A Module is not a valid type error

    Hi All, I am getting "A Module is not a valid type" error while trying to run below code. This code works fine when it is in the same file where data is saved. But giving error when being run from Personal.xlsb workbook. Unable to understand it is happening. Function FilePath() As String Dim...
  11. D

    VBA help on saving selected range as picture

    Dear All, I need help on saving a specific range as a picture. I found a code at https://stackoverflow.com/questions/38367064/operating-ms-paint-using-excel-vba but a line of code not working and no solution related to this found. Sub Saving_Pic_of_a_Range() Dim path As String Dim...
  12. D

    Need Dashboard creation help

    Hi, I need an urgent help to create an interactive dashboard which can give multiple insights using the data file I have uploaded. Management needs insights specially basis Teams, Function, Location and software type. With there new queries all the time, I am able to think of only pivots but...
  13. D

    Need to unregister inactive users after 60 days

    Hi, We have issued few licenses to users and they need to utilize them within 60 days. If any user doesn't use it within specified time period, need to unregister that perticular user. I want to apply a conditional formatting or any other kind of indicator which should inform me if time...
  14. D

    Need help with Thermo-meter chart

    Hi Friends, I have created a chart similar to the thermometer chart in the same way as published on chandoo.org (http://chandoo.org/wp/2012/06/11/thermo-meter-chart-with-last-year-marker/) and it is working fine. Only difference is that there is no Last year marker so there are only two...
  15. D

    Data validation problem

    Hi All, I have a problem with data validation and unable to find its solution. I have an excel sheet which I am unable to upload for your reference but Lets say column-A has names, Column-B has mobile numbers and column-C has a drop down list from where person need to choose city name...
  16. D


    Hi All, Range A1:E1 has values 100,200,300,400,500 and Range A2:E2 has values Agent1,Agent2,Agent3,Agent4,Agent5. Now somewhere in the sheet I apply the formula =HLOOKUP("Agent4",CHOOSE({2,1},A1:E1,A2:E2),2,0) but it is giving me #N/A error. I don't know why it is not giving me the right...
  17. D

    Dynamic Chart Range

    Hi All, I have more than 2 charts in my workbook and there is a chart for which I need to change source data range dynamically. I have done all the calculations using VBA code but the only problem is that I am unable to set this calculated range value to the chart property. Reason for above...
  18. D

    Problem to print sheet names and convert names as links to those sheets

    Hi All, Kindly help me with the problem. I am required to print names of all the sheets in a workbook and make these names as Hyperlink to respective worksheets. I am able to print sheet names but don't know how to convert them as hyperlink. Thanks, DJ
  19. D

    How to remove unwanted files?

    Hi All, Every timne when I open a new workbook, there are 4 files that open and I want to disable them. Those files are (ATPVBAEN.XLAM, EUROTOOL.XLAM, SOLVER.XLAM & FUNCRES.XLAM). I have checked the XLStartup path and only Personal.xlsb is there. Plz help to remove unwanted files...
  20. D

    Inserting a blank row after every alternate item of a list

    Hi, I need help with a problem. Please help. I have a list in a column. Lets say Sheet1 has a list of names from A1:A50 and I need to insert a blank row after every item of the list. I tried to select every alternate row and insert new row but it inserts a row afer every 2nd item which is...
  21. D

    PivotTable data range

    Hi, I have created a pivot table based on some data. The problem is that every time I update data, I am required to delete the entire data (Actually not simple delete but need to remove entire rows which have data)first and then paste the new data then I refresh the pivot. Now problem comes...
  22. D

    Invisible character

    Hi, I received some data from our business partner which has some employee names and their details. Employee's First name and last name should be seperated by a single space only. However, there are many names which seems have two or more spaces. I checked that actually there is single space...
  23. D

    Custom formatting

    Hi, In reference to the technique mentioned at link: http://chandoo.org/wp/2012/05/07/displaying-text-values-in-pivot-tables-without-vba , I want to know if this technique can be used to display a different text in place of the actual text. I tried but it didn't work. Please suggest if any...
  24. D

    Data Validation

    I have applied a data validation in a cell so that user can select values from a list only. However, if user copies any other value different than the list values and paste on the cell having data validation it(cell) accepts this new value. I actually want to restrict user so that he/she is...
  25. D

    DisplayGridlines property

    Hi, I have a query regarding a VBA code that is ActiveWindow.DisplayGridlines = True/False. Why do I need to refer to the active window in the above mentioned code line? If I give a reference to a worksheet that is not active it doesn't work e.g. if write...