• 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

    Hui...

  • 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. M

    Spread Variance to other Cells

    I have attached an example spreadsheet. I am trying to spread the variance into cells in column E via a formula. Currently it is manually. Cell F21 must equal 100%. Any ideas? Thanks!
  2. M

    Trend Function to Ignore Zeros

    I am trying to look at sales data for many stores. Some stores were not open at some points in time. For instance, maybe 3 stores did not open until 11/16 while others were open from 07/16 to present. I want to use the TREND function but want it to ignore the zeros. I did find a...
  3. M

    Power Query Use Data From Worksheet as Parameters

    I am pulling data from a database. What I would like to do is setup a few parameters in the workbook that contains the query and use named ranges to filter the data in the query. For instance have a start and end date, service, the beginning of a billing code such as PR, CB, or FB that I enter...
  4. M

    Calculate Trailing 12 Months - Power BI

    Anyone have any ideas on how to calculate a trailing 12 months in Power BI to create a graph showing how the client base has changed over time during a 12 month period? My calculation in Excel is Retention Rate = (End Bal. - New)/Begin Bal. I have data where there is a start date and an end...
  5. M

    Need advice on macro

    I have a macro that creates several folders, then takes data from one spreadsheet and creates several spreadsheets and saves them to a specific folder. Next I need to add several Call Macros to format each file in each folder, there are 9 folders, what is my best option? Should I setup a...
  6. M

    Create folder and save spreadsheets there based on criteria

    I have a spreadsheet with data in columns A:N, headers are in row 1. Currently my macro takes the client name (there are 24 clients) in column N and creates a spreadsheet and saves it to a folder. In column N there are the names of the vendors associated with the clients. What I would like to do...
  7. M

    Power BI Date Ranges

    I have a table called SInvoices that has a column called invoiceDate. I am using a slicer to select a date range. I have another table called Commissions that has a column called startDate and another called endDate. I would like to have it when the slicer is used to select the date range it...
  8. M

    Power Query Need Some Filtering Help

    I am kind of stuck and need an outside opinion. I have some data where the client has a specific term date, which is easy to filter. However, there is also a column where the client is either active or terminated. The issue is that in some cases the client is active but has a end date and in...
  9. M

    Power Query Filter Multiple Columns

    How do I create a filter that says IF column A begins with "C" and column D contains "Apples" then exclude that data from the query? Thanks!
  10. M

    Count rows between repeating values

    In the example spreadsheet in column A is an ID# and in column B is the relationship. In row 2 there is the employee column B and their ID# in column A. In rows 3-5, they are related to the employee in row 2. What I am trying to do is in column C to have the ID# repeat until Employee is found...
  11. M

    Power BI Count between ranges

    I have a query setup with all the parameters needed. All I need now is to be able to display my data in groupings like this: 0 to 25 26 to 50 51 to 100 101 to 150 151 to 200 +200 What is the DAX counter part to COUNTIFS? Thanks, Mike
  12. M

    Question About Working Macro

    I have this macro that works but it asks for me to choose the file fName1 three or four times. Why? Sub GetFile() Dim fName1 As Variant, wbo As Workbook Dim fName2 As Variant, wba As Workbook Dim WBName As Worksheet fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS)...
  13. M

    VLOOKUP in Macro

    I am working on some code where I identify a workbook as "wbo" by way of GetOpenFileName. Set wba = Workbooks.Open(fName1) I am trying to do a VLOOKUP like this: Range("O2:O15").FormulaR1C1 = "=VLOOKUP(RC[-1],wbo R2C15:R150C15, 1, 0)" How do I reference wbo in the formula? Thanks.
  14. M

    Web Scraping Help

    I am wondering if someone can help me with this. I know very little about this type of VBA coding. I can scrap simple data, but this is a challenge. So here I will say many, many thanks! I have a list of numbers in an Excel spreadsheet and want to loop through them to extract data from the...
  15. M

    Macro Freezes

    I have a macro that has been working for a while. For the last two months it has not been working. It pulls the data into the spreadsheet from Access and then just freezes. There are no errors, no nothing, just the little blue circle like it is doing something. But it just hangs there. Can...
  16. M

    3D Reference w/ Multiple Worksheets Not Working

    The scenario: I have a workbook with140 worksheets with various names. I used a macro to extract the worksheet names. I pasted those names in M1:M140 and gave it a named range of Tabs in the Totals worksheet. In the 140 worksheets in cell B3 is one of 5 unique names that I want to sum on. The...
  17. M

    Run-time error '1004' Not sure why? Code runs well in one place.

    I have a macro that errors out on the line of code that says ActiveWorkbook. The exact same code runs well in one place but not the other. Any idea why? sFileName = "MN Packout Log " & Format(Now, "mm-dd-yy") & ".xlsm" sBasePath = "C:\General Use\UPS Daily Tracking\Logs\"...
  18. M

    Copy 3 named ranges into 1 and paste value

    OK, I know this is simple, but I have 3 named ranges in worksheet1 and want to combine data from 3 named ranges into one and paste value that into worksheet2. Range 1 = COID Range 2 = CoSet Range 3 = Service New Range = COID&CoSet&Service I would end up with something like this in columnA of...
  19. M

    Pricing Matrix & Monday Brain Fog

    Hello fellow excel users! This morning I am having brain fog and need a little help. Perhaps I am overthinking this. I have a pricing scale where in range B2:B7 I have the following: 1,2,3,4,5,All In range C1:G1 (Qty size) I have 1 to 250, 251 to 1000, 1001 to 3000, 3001 to 5000, 5001+...
  20. M

    Set cell to default value when closing workbook?

    Is there a way to set a cell value to a default value when a workbook is closed without VBA? I have a dropdown list in cell A14 that has a list of names. When a user closes the workbook I would like for that cell to say, "Select your name." when the next user opens the workbook. Is this...
  21. M

    VBA to extract matching data from Access

    I have a list of ID numbers in column A of a spreadsheet. I have an Access Query called Billing Details. In this query, "co" matches the ID numbers in column A of the spreadsheet. What I want to do is have VBA in the spreadsheet loop through each ID number and return all the data associated...
  22. M

    Average without multiple min and max values

    I have some data which has some MIN values way below the average and some MAX values way above the average. How do I eliminate the MIN and MAX values to get a "True" average? Example: 150,000 110,000 90,000 20 rows between 15,000 - 25,000 5,000 1,200 1,000 How would I eliminate the...
  23. M

    Macro worked, now it displays Run-time error 91

    This is just crazy, this macro has been running for a while and now it does not. For whatever reason now wbIC and wsIC shows "Nothing". The error occurs on this line of code: With wsIC 'sort asc in Col D .Cells(1).Sort Key1:=.Range("D2"), order1:=xlAscending, Header:=xlYes End With To me...
  24. M

    Find Max value and return Date

    I have some dates in C1:N1 and data in C2:N4500. I have the max value of each row of data in column O2:O4500. I would like to in column P return the corresponding date of the max value from row 1. I have this formula in column P to get the cell address of the max value...
  25. M

    VLOOKUP with Dynamic LOOKUP & Return Range

    OK, not sure how to explain this idea, so I have uploaded a workbook with what I want to do. Basically, it is a Balance Sheet that has headers for each section of data, Cash, AR, Other AR, etc., the issue is I cannot use VLOOKUP because each "section" has Total instead of Total Cash. My end...
Top