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

    VBA: formula calculation on dynamic range

    Narayan, This looks promising. Can you explain what the RangeParameter does? Thank you everyone for the input!
  2. J

    VBA: formula calculation on dynamic range

    Hi there, I have a pretty large macro that I run to validate the accuracy of data in a file. Right now the code has 100 + formulas referencing static ranges and performing calculations. The only problem is as the file grows in size with time, these formulas will need to reference the entire...
  3. J

    Calculating Monthly Payment Date

    I want to build a formula that I can drag down to replicate the string of dates in the attached file. Based on a start date and anniversary date, I would like to have the date increase each row by one month, with the same day value as the anniversary date, unless that day falls on a weekend or...
  4. J

    Heavy Nested IFs

    Hi, I would like some help using multiple nested IFs and concatenation to develop timestamps. See the attached file. Column A: The timestamp "code" (d=days, h=hours, m=minutes, s=seconds) I would like applied in concatenation. Column B: The time data, in hours. Column C: My current solution...
  5. J

    Calculate Average from Multiple Data Sets

    Would SUMPRODUCT still work if we added another level? So imagine we add another table: Table 1 contains a list of Car Parts and their respective cost. Table 2 contains a list of Cars, and the Car Parts that belong to them. In the sample file, each Car has 2 Car Parts. Table 3 contains a list...
  6. J

    Calculate Average from Multiple Data Sets

    Do you mind helping me understand the logic of the formula?
  7. J

    Calculate Average from Multiple Data Sets

    Awesome, thank you so much!!!
  8. J

    Calculate Average from Multiple Data Sets

    Hi, I am having trouble conceptualizing which formula or combination of formulas to use in this scenario. . . Imagine 2 data sets that come from 2 different database tables: Table 1 contains a list of Car Parts and their respective cost. Table 2 contains a list of Cars, and the Car Parts...
  9. J

    Pivot Table Filtering by Value

    Thanks Luke & Narayan!
  10. J

    Pivot Table Filtering by Value

    Luke, Can you tell me how you created that calculated field? That is exactly what I want to do, just have it replicated on a larger scale. Did you create the field like this?:: I am wondering if there might be another way to create this calculated field without referencing each individual...
  11. J

    Pivot Table Filtering by Value

    Hi Everyone, I have attached a sample of what I would like to accomplish using a Pivot Table. Say I have a group of individuals to whom I would like to distribute samples of my products. Say that I have a limit of 2 on how much of any product I should give any one individual. The table in the...
  12. J

    Updating Conditional Formatting with VBA

    You're right. My solution to his problem probably went in a direction that was not the best, most efficient and simple solution. So please allow me to take a step back with this explanation: The first tab of that file is what my friend sent me. He asked me how he could sort by column B ('Sbtl...
  13. J

    Updating Conditional Formatting with VBA

    Hi All, I have created a materials order tracking log (attached) for a friend. The ‘Orig’ tab is what he gave me, and initially had used colored rows to separate the different systems (column F) which caused him problems when sorting. My solution on the ‘Work’ tab was to conditionally format...
  14. J

    Visualizing Survey Text Responses

    Thanks Hui. Any chance you can provide the workbook used for the link you gave above?
  15. J

    Visualizing Survey Text Responses

    Hi Chandoo.org, First off, I want to shout out my appreciation for this site: Recently I was in an interview for a Business Analyst position (praying for an offer!!) and I razzle-dazzled the interviewers with three nuggets of Excel wisdom that I learned directly from Chandoo.org! 1)...
  16. J

    Conditional Formatting to Highlight Specific Row in Data Set

    Thank you Nebu & Hui! Can anyone explain why N1 is needed and why the CF cannot just be =$B8=$B$1 ?? ** Nevermind! The CF can be made simpler! **
  17. J

    Conditional Formatting to Highlight Specific Row in Data Set

    I have been playing around in Excel and I have some US presidential data that I want to make a little interactive. I am really just messing around with INDEX(MATCH()) and Conditional Formatting. Right now I have it set up in cell B2 that you can select an ID tied to a specific president with...
  18. J

    Charts: Plotting Daily Values by Month?

    Hi everyone, Using the method described above, this is how my chart looks: Is there any way within the chart options to remove the ugly days area but keep the formatting of months, like the following cropped photo: As you can see, I am gaining weight and trying to get back down close to...
  19. J

    Charts: Plotting Daily Values by Month?

    Thanks for your time Narayan. This is how I want the axis to look, however, the chart no longer reflects the daily changes. Are the two mutually exclusive? I figure there has got to be some workaround so that you can still see the spikes and dips of the daily data but with a cleaner...
  20. J

    Charts: Plotting Daily Values by Month?

    Hi, I have daily data that I graph with an area chart. How can I get the graph's x-axis to plot by month? So in the attached file with 3 months of data, the x-axis should only read Jan-Feb-Mar. Is this possible? It would be really nice if the labels could be aligned with the data. Thanks.
  21. J

    VBA Compatibility Issue with Excel 2013

    Hi everyone, I was inspired by this interesting article to incorporate a pop-up calendar in a file I have created for a colleague. I opted not to use the Microsoft Date and Time Picker Control 6.0 and instead use the Microsoft MonthView Control 6.0. The code uses a named range and...
  22. J

    Protecting Cells

    Narayan, thank you for this MUCH simpler solution!
  23. J

    Protecting Cells

    Hi All, I have attached the file I am working on, which is designed to be used by pretty much everyone in the company. The user selects from a dropdown in column B. I have vba that populates a 'billable rate' in column D based on this dropdown selection. I want the yellow highlighted portions...
  24. J

    Understanding RegExp

    Hi all, I have been picking apart an Excel file with a lot of code in it, in an attempt to recreate the file and understand the vba behind it. It seems the public functions are all being initialized with the same structure, declaring a variable as a "RegExp" which I believe stands for regular...
Back
Top