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

    Hyperlink with filter or passing criteria

    Here is a simple example of what I am trying to. I have a column of data, Hospital readmissions. There are 6 values in this column Hospital readmissions 7 12 9 4 7 2 The formula to calculate each value looks like this...
  2. D

    Hyperlink with filter or passing criteria

    I guess my vision is to be able to do something like passing data with key-value pairs like we do on a querystring for a web applications https://chandoo.org/excel?filtercriteria1=apples&filtercriteria2=red&filtercriteria3=Jan well , something like this for a value in a cell linked to worksheet
  3. D

    Hyperlink with filter or passing criteria

    Hi all, I have googled this question and not come up with a solution that fits my needs yet. I have a dashboard with hospital readmission rates, basically numerators, denominators and %rate values , for each quarter of the year. What my question is how can I make the values link to another...
  4. D

    Paste Special - Values only not working as expected

    Luke, you are right its anoying since I had the values I wanted completed in about 20 mins then I spent the next few hours searching and tshooting to try to get rid of the "empty" cells. I did get to converse with all you fine folks about excel, which is always a pleasure. I did solve my problem...
  5. D

    Paste Special - Values only not working as expected

    I copied the data to another sheet and used paste special > values , then I used filter to get rid of the blanks. This worked so I am all set. Still dont understand why my other method used find/replace > go to special > blanks and I couldnt find any blanks even though i had some blank cells...
  6. D

    Paste Special - Values only not working as expected

    Hi, I have a formula in a worksheet that I copied down aprox 61K rows. The formula looks like this: =IF($A4=$H$1,OFFSET($A4,-1,1,1,1),"") so when this formula is false I get several "blank" rows. When I copy this column to another sheet and try to paste special values only, I see my blank rows...
  7. D

    How do I reference a chart series

    Yes, thanks all, upgrading to MS Office SP 3 resolved this issue. I am able to now crawl out of the rat hole I found myself in. Dave
  8. D

    How do I reference a chart series

    Thanks, acording to that article it requires a service pack update. I will try to get that installed. Thanks Dave
  9. D

    How do I reference a chart series

    Not sure if the title of this thread is even close to my problem. I have a chart that updates the last N number of values. It uses the named ranges and the offset function. The way i have this set up works on my PC fine but as soon as I open the file on another computer it fails. My theory is...
  10. D

    please can you help me

    pmcc1, In Excel dropdown lists are a form of data validation. There is a really good tutorial right here on Channdoo.org. this should get you started http://chandoo.org/wp/2008/08/07/excel-add-drop-down-list/ If you want to ge more complex , meaning context sensitive you can do that too...
  11. D

    Advice on maintaining long formulas

    Narayan, Thanks so much for taking the time to download my file and help me. Some more details that will help explain what my objectives are. The CAR-Dashboard sheet is a legacy documemnt, meaning I cant change the layout and the the sheet should be read-only to my users, meaning they enter...
  12. D

    Advice on maintaining long formulas

    @Narayan, Hi I have sanitized a sample of my workbook. Can you help me to understand some of my options to manage such long formulas. Maybe an example of a UDF you suggested? I wasn’t able to get a udf to do what I wanted. Is it possible for a UDF to change the color of a cell...
  13. D

    Advice on maintaining long formulas

    Hi Narayan, I do know some VBA and I have been torn as to redo this project using vba. You are so right about trying to understand this formula in the future. Thanks, Dave
  14. D

    Advice on maintaining long formulas

    Hi Hui, That first if statement doesnt look for an error but it looks for a value in one of my dropdown lists on the data entry page. If the value is 0 then nothing should display. That would help a little but not really solve my problem of having to maintain this formula on a worksheet in...
  15. D

    Advice on maintaining long formulas

    Hi all, I have been working on a data entry project in Excel. I have a Data Entry worksheet that has about 13 columns. some of the headers are: Indicator Name Indicatory Category Date Goal Facility Unit Unit Group Numerator Denominator Base Rate Calculated Rate This worksheet...
  16. D

    sumproduct on range containing vlookup formula

    Hi Hasseb, Thank you. That fixed my problem. Dave
  17. D

    sumproduct on range containing vlookup formula

    Hi Hasseb, Thank you. That fixed my problem. Dave
  18. D

    sumproduct on range containing vlookup formula

    Hi, Rate_Base is a named range with empty cells, when I put values in the cell it correctly sums the values of rate_base =SUMPRODUCT((MONTH(Entry_Date)=1)*(YEAR(Entry_Date)=2013)*(Indicator_Name="CLABSI")*(Facility="Carrollton")*(Unit_Group="ICU")*(Rate_Base)) Why does this not work? If I...
  19. D

    Conditional formatting with a named cell

    Luke, I will have to try this out, I guess maybe I should rebuild my whole project in VBA. My formulas are getting so big they wrap 3 lines in the formula bar. So I should be able to write all my formulas in VBA and wrap them with a Evaluate function? I give this a shot Thanks Dave
  20. D

    Custom data validation - formula doesnt evaluate

    Hi Luke, Sorry for the delay in my reply. I will have to take a look a this again. I made several attempts at this so I will see if my values are correct. Thanks Dave
  21. D

    Conditional formatting with a named cell

    Hi, I have a conditional format that works fine but what if I want to assign an named cell as my criteria instead of hardcoding a value. Seems like this should work? Hardcoded version...
  22. D

    Custom data validation - formula doesnt evaluate

    Luke, Sorry, I am able to enter the formula as my custom validation criteria. I dont get an error. It just doesnt work, meaning I should get my warning message when I enter values that violate that rule/logic. So , I should be able to do this? Dave
  23. D

    Custom data validation - formula doesnt evaluate

    Hi all This has had me stumped for a while. I have cell M2 containing this formula in it =COUNTIFS(Indicator_Name,A2,Entry_Date,DATE(YEAR(C2),MONTH(C2),1),Facility,E2,Unit_Group,G2) If I set a custom validation on cell L2 to this =M2<=1 If a user trys to enter a value in L2 greater...
  24. D

    Title should read Dynamically adjust X axis time scale with vba

    I guess the VBA forum would be a better place to ask this one? Hi, First, Is it true that a regular line chart cant scale the Y axis in hours? so we should use a scater plot? Here is my real question, My hospital takes temp and humidity readings every 30 mins for 11 Operating rooms. This...
  25. D

    Dynamically adjust X axis time scale with vba

    Sorry, can I edit a post. Title should read Dynamically adjust X axis time scale with vba
Back
Top