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

    Extract from Access to Excel

    stSQL1 = "SELECT * FROM serviceinfo" The "*" means select all data in a sql statement. If you dont want them all then you need to do something like: 'strSQL1= "SELECT [TABLE1], [TABLE2] FROM [ServiceInfo]" that will give you the data of table1 and table2 (of course you would use the...
  2. T

    Hide Blank series in a chart

    Glad it helps. About 90% of what I learned, I learned from here regarding the formula from luke, basically it comes down to that if a cell has #N/A excel will just ingore it. So could use the NA() function in other forumals
  3. T

    Hide Blank series in a chart

    Take a look and see if that helps you at all. There are two ways that I can see go about accomplishing, what I think you want https://www.dropbox.com/s/qosk0anzrcfsc48/hide%20Chart.xlsm
  4. T

    If cell contains ...

    See if this helps: https://www.dropbox.com/s/akot2uhrthbpw4z/Find%20abc.xlsx
  5. T

    Highlight Max/Min Data Point on an Excel Chart

    This should get you started in the right direction: https://www.dropbox.com/s/w7w5ff5my6t0qnq/Min%20Max%20Chart.xlsx
  6. T

    how do I display the target in a graph as well

    Lets see if this is something you are looking for: https://www.dropbox.com/s/ic1874v7d29wdrh/Target%20Graph.xlsx
  7. T

    vba code running slowly.

    just some quick observations: 1) Near the end of the code, where you color the cells, I would just use the conditional formatting thats built into excel. I always try to use as much that is native to excel as i can 2) I havent really dove into this code, but seems to be a of "select"...
  8. T

    suitable formula

    When you have list like you do and looking for 1 value, using IFs, vlookups, etc can get quite cumbersome. I have attached a simple function that uses the Select Case to create a simple UDF. If the selected fruit is empty, then will show "Please enter a fruit", but can be easily changed to...
  9. T

    Adding a calculated field in Pivot Table to use in Pivot Chart

    Calculated columns in pivot tables can be helpful, but they are often too simple. I would take a look at that the "GetPivotData" function. Can be a little intimidating at first, but once you dive into it's quite easy and makes using pivot table & calculated columns much easier.
  10. T

    Breaking address field

    Not a problem at all. Glad you found it helpful
  11. T

    Breaking address field

    This should get you started in the right direction. Of course it can be as simple or complicated as you want to make it. https://www.dropbox.com/s/meb1j5aoc6ytxkp/Address%202.0.xlsx
  12. T

    Sales Comission

    Something like this seems perfect in using the Select Case method in VBA.
  13. T

    Countif returns wrong result

    I took a quick look, and I would have to guess that since the numbers are so big ( when I copied and pasted into my excel it gave me x.xx+Exx) so using the countifs it probably only goes out so many numbers. Probably need to break that number up into some a little more manageable
  14. T

    trying to create pivot table from vba code

    vandanarana, What I have found to be a lot of help when I'm having trouble with some VBA, is that just turn on the recorder and do whatever I'm trying to do ( assuming it can be done via the recorder). Of course it won't be perfect, but it will give you a good head start of what needs to be...
  15. T

    Running monte carlo simulation

    for these types of simulations, I have found that using the solver add-in is the best method bar none. I guess it's how involved and how much time you want to invest. Stats in excel is pretty vast area, there are a few good books out there, but the one I have found to be invaluable is...
  16. T

    Project Management Dashboard

    I think the attached will get you on your way. Everything that it does I learned here from Chandoo, both in the free and paid services. If you are at all serious about xl, then I would highly recommend the classes. Anyways... *I made some assumptions and filled in random data for the rest of...
  17. T

    Formula for calculating moving averages

    This should get you started in the right direction. Offset can be a little tricky, but it's very powerful in order to make your excel dynamic. Chandoo has great free tutorials on this site, so I would look at that. https://www.dropbox.com/s/1hczqes6agx413j/Rolling%20MAs.xlsx
  18. T

    Pivot Table - Calculated Fields

    I always found adding calculated fields in pivot tables to be somewhat restrictive. Only makes sense though, since the data is coming from only one source. A couple of ideas: 1) Connect your data to PowerPivot add-in. You can do much more with pivot tables that way. 2) Use your cube...
  19. T

    Cleaning up Messy Data

    Just took a quick look, is there anyway you can upload the .csv file? Seems odd to have the dates going across instead of down. Since you got a table how you want it. If the newest data will always becoming in the same way, seems relatively straight forward to able to create a macro to import...
  20. T

    can I ask a SQL question here?

    I'd say go for it. Though SQL is a very broad subject, so who knows if you will get an answer. I myself, have been learning T-SQL. Just seems like a natural progression. Learn XL to manipulate the data, then learn some SQL to efficiently get the data.
  21. T

    average function includes blank or #N/A

    If you are using excel 2010, you can always use the new "Aggregate" feature. Though since it's a new function it's not backwards compatible.
  22. T

    ZOOM IN ON MICRO PIVOT CHARTS

    Always interested to see how other people do things in Excel. I think that's the great thing about xl, is that there are so many ways to accomplish it. I like what you have here, so always something new to look at. What I have done was just assign the macro to the actual picture of the...
  23. T

    ZOOM IN ON MICRO PIVOT CHARTS

    Best I could come up with. Still getting through the VBA classes to get much better at VBA, but at least it's start.
  24. T

    ZOOM IN ON MICRO PIVOT CHARTS

    Maybe this will help you in what you are looking for: http://www.2shared.com/file/bKfrFeuh/Chart_Zoom_Example.html *my apologies if this reply shows up twice
Back
Top