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

    Conditionally format text typed other than yes and no

    just brilliant. i never thought of doing this the law of opposites. thanks again Hui for solving this one.
  2. V

    Conditionally format text typed other than yes and no

    Thanks for your reply but my question was if the user types in anything other than yes or no it should be formatted. With yoru answer when the user types in ABCD CF will be applied. I worked around the problem by limiting the user to a list box with the only 2 options yes or no to pick from...
  3. V

    Conditionally format text typed other than yes and no

    Thanks for your reply but my question was if the user types in anything other than yes or no it should be formatted. With yoru answer when the user types in ABCD CF will be applied. I worked around the problem by limiting the user to a list box with the only 2 options yes or no to pick from...
  4. V

    Conditionally format text typed other than yes and no

    Hello All, I am using Excel 2007. I know how to conditionally format a cell if it is blank Or say cell value = “yes” or cell Value=”no” color the cell. How to cf if the entered text is anything other than yes or no than turn red. Any pointers will be appreciated. Thanks again for...
  5. V

    conditional formatting based on multiple cells

    Hello All, I have a task were i need to format cells based on conditions. The spread sheet is available here https://docs.google.com/spreadsheet/pub?key=0AisY7zbvSsm4dGlvZWNDcDBzdWlPWmZWbkR3LTdUcXc&output=html On the left hand side i have details of employee training schedule with dates...
  6. V

    excel logic help required

    I will work on this logic to get my results thanks once again. appreciate your time.
  7. V

    excel logic help required

    Thanks for taking the time to create this xl sheet. it is very useful and i can customize it for my requirements. I currently am using countif to count the number of employees signed up for a course. since the dates are listed as month/day/year i used some helper columns to derive the year(date)...
  8. V

    excel logic help required

    My dates are mm/dd/yy. Course 1 validity is for 3 years , course 2 is for 2 years and course 3 is for 4 years. Also i first need to calculate individual participant validity if their course completion has expired or they are still valid till another year. each course has a different course fee...
  9. V

    excel logic help required

    I work with excel 2007. I have an excel spreadsheet which requires some calculations. Calculating the course fee that the company will incur this year to run the refresher program. The dates are when the employees last did the course. Eg. participants course 1 course 2 course 3...
  10. V

    conditional formatting based on another cell value

    I use excel 2007 I have selected use a formula to determine which cells to format Can I use the formula for a range of cells like this =AND($C12:$C200 <> "",$D12:$D200 <>"") format some color. Thanks again.
  11. V

    conditional formatting based on another cell value

    Hello All, I would require some conditional formatting insight and help here. I have values in Col A and some hours in Col B Col A | Col B Task 1 | 23 Task 2| 44 How to apply conditional formatting to the entire col –A such that it should fill up with a color if the col B is empty ( col B...
  12. V

    match function help with forumla required

    thanks that worked like a charm
  13. V

    match function help with forumla required

    I have a match function that selects the code and returns the position =MATCH("G222",C1:C70,0) How to change the formula to say if match found then in cell S20= completed or if not found then S20=incomplete when a match is not found it gives #NA how to work around that. appreciate all...
  14. V

    sum product clarification

    Understanding these codes. =SUMPRODUCT(((LEFT($C$12:$C$1000,1)="A")*$D$12:$D$1000)+(($C$12:$C$1000="")*(LEFT($C$11:$C$999,1)="A")*$D$12:$D$1000)) Xld code why do we have 1 cell less (999) than the set range of 1000 Hui code =SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="b"),($D$12:$D$1000)) Both the...
  15. V

    sum product clarification

    ok xld solution =SUMPRODUCT(((LEFT($A$12:$A$200,1)="A")*$B$12:$B$200)+(($A$12:$A$200="")*(LEFT($A$11:$A$199,1)="A")*$B$12:$B$200)) eliminated the problem and i can insert a new task inbetween 2 tasks. I really thank Hui and xld for sticking with me through all my rambling till i found out...
  16. V

    sum product clarification

    I found out the reason for this strange behavior. I have a autosum function at the end of the hour column. Lets say the total sum of hour 26 and the last code value was 2 it gave me 28. The moment I deleted the autosum it gave me the results. On further testing I find that if a user enters a...
  17. V

    sum product clarification

    Looks like I may have the concept wrong. I have series of alphanumeric codes eg. A677,A987,A908,B897,B87,B990,C999,C766,F998 My intent is to sum all the occurrences of A,B,C,F and so on. The sum product function that you had defined worked fine until I realized that a user was not inputting...
  18. V

    sum product clarification

    Hello Hui, thanks for your input. The =SUMPRODUCT(--(LEFT($A$12:$A$1000,1)="A"),($B$12:$B$1000))+SUMPRODUCT(--(LEFT($A$12:$A$1000,1)="A"),($B$13:$B$1001)) Seems to have a cumulative total effect and does not give me the actual results. For eg. if the sumproduct of A= 12 and if i apply...
  19. V

    sum product clarification

    <iframe width="402" height="346" frameborder="0" scrolling="no" src="https://r.office.microsoft.com/r/rlidExcelEmbed?su=2077200618899905946&Fi=SD1CD3B2F518C01D9A!131&kip=1&wdAllowInteractivity=False&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True"></iframe> not...
  20. V

    sum product clarification

    Hello Hui this is the file https://skydrive.live.com/?cid=1cd3b2f518c01d9a&group=0&sc=documents#!/edit.aspx/.Documents/Book1.xlsx?cid=1cd3b2f518c01d9a&sc=documents&nd=1 i hope you can open it. XLD solution gives cumulative summation and is not the desired result.
  21. V

    sum product clarification

    Yes I am summing up the values 2 & 4 are in the second column but the reference i.e C099 and E98 are in the next line. So when I do a =SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="C"),($D$12:$D$1000)) So when I do a =SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="E"),($D$12:$D$1000)) It sums up all the codes...
  22. V

    sum product clarification

    SUMPRODUCT CLARIFICATION A88 20 C999 10 C099 2 E888 12 E98 4 Sum product will be A = 20, C=12,E=16 = total 48 Lets say a user inputs values like this A88 20 C999 10 C099 2 E888 12 E98 4 Sum Product is A= 20, C = 10, E = 12 total = 42 Is there any way I can capture the...
  23. V

    sum if function - help required

    I tried this macro to copy from my worksheet into a worksheet call ANALYZE. Macro Range("P21:P26").Select Selection.Copy Sheets("ANALYZE").Select ActiveSheet.Paste End Sub i have this macro which selects the cells P21:p26. Since this is a calculated cells with forumlas it is copying only...
  24. V

    sum if function - help required

    I would like to go a step further and use a button were a sheet 2 is opened and sumproduct of sheet1 gets calculated when the update button is pressed. =SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="A"),($D$12:$D$1000)) =SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="B"),($D$12:$D$1000)) etc. i would like to...
  25. V

    sum if function - help required

    Hello luke, =SUMPRODUCT(--(LEFT(F26:F38,1)="A"),(G26:G38)) i had omitted the -- in the formula the first time. is this data for the first array. i have got it working thanks again for all your help. now it works with one forumula
Back
Top