• 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. Sam Mathai Chacko

    Find Month Between two specified date

    A simple text formula should do the trick =TEXT(A2-7,"mmm yy")
  2. Sam Mathai Chacko

    Hi Hui, Hope you are doing well. I haven't been able to spend much time online, but I got one...

    Hi Hui, Hope you are doing well. I haven't been able to spend much time online, but I got one of your posts (http://chandoo.org/wp/2015/03/27/conditionally-format-chart-backgrounds/), and thought I'll share one more solution. But I don't know how to attach the file. If it's fine with you, I...
  3. Sam Mathai Chacko

    Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

    This keeps getting better. 64 Characters =SUM(OFFSET(C2,,,IFERROR(MATCH(0,N(LEN(A2)<LEN(A3:A$20)),),19)))
  4. Sam Mathai Chacko

    Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

    Thanks Faseeh for the feedback. Just realized that I could take out 5 more characters :) =SUM(OFFSET(C2,,,MATCH(0,N(--SUBSTITUTE(0&A3:A$22,".",0)>--SUBSTITUTE(A2,".",0)),)))
  5. Sam Mathai Chacko

    Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

    No takers? I understand it's relatively simple compared to a lot of the other formula challenges, but that shouldn't make you abstain! Well, I'll go ahead and post my solution =SUM(OFFSET(C2,,,MATCH(0,--(--SUBSTITUTE(0&A3:A$22,".","0")>--SUBSTITUTE(A2,".","0")),)))
  6. Sam Mathai Chacko

    Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

    Narayan Of course that will work, but again, the idea is that the levels could be as long is it can... maybe 3 digits or 4 digits. Your original formula is fundamentally correct, but maybe could be a little more generic, to the extend that it considers any number of levels, and not just up to...
  7. Sam Mathai Chacko

    Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

    I might add that the only problem being, if the level exceeds to two digits, like 10, 11 or above, then the solution might not work exactly as intended
  8. Sam Mathai Chacko

    Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

    Narayan, that looks very much right. And shorter too. Great :) ... any other attempts anyone?
  9. Sam Mathai Chacko

    Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

    Sure Narayan. File attached in OP.
  10. Sam Mathai Chacko

    Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

    So we haven't had a new challenge in a while, so I thought I'll remind people that we have a section of the forum to pick your brains, by posting the following formula based challenge. I came across this in VBAExpress; so the credit for the question goes to the OP. So anyway, the challenge is...
  11. Sam Mathai Chacko

    Ribbon DatePicker Calendar Control For Excel 2007-2010

    Thanks Bob for the kind feedback. Will certainly post here if I get around with further tweaks.
  12. Sam Mathai Chacko

    retrieving data from mails(outlook) using excel/vba

    dan, I am not sure mate. I think I just about manage to get by. :)
  13. Sam Mathai Chacko

    retrieving data from mails(outlook) using excel/vba

    Good to be back after a while, though I don't know for how long. So first thing's first.... webmax, were you able to get it to work eventually, or did you need any help. Cheran, can you explain your requirement. What I think you want to do is to push data in to different workbooks for each...
  14. Sam Mathai Chacko

    How does this code open up another file?

    And yes, sHistFile is getting it's value from the range Range("rngHistFile") which is in sheet contained in the variable shtHistStock
  15. Sam Mathai Chacko

    How does this code open up another file?

    Yes, you can add a line MsgBox sHistFile within that sub-routine somewhere to throw you a message with the content of that variable. Alternatively, you could use Debug.Print sHistFileand go to the immediate window in the vba editor by pressing CTRL+G
  16. Sam Mathai Chacko

    How does this code open up another file?

    It is opening the file from the same path as your originally opened workbook. ActiveWorkbook.Path does just that.
  17. Sam Mathai Chacko

    Formula Challenge 022 - Find the item with the largest sum in a list

    I echo Sajan's views. Thanks once again Lori.
  18. Sam Mathai Chacko

    Minimum amount to be shown from multiple data sheets

    Deb, no reason for SUMPRODUCT. You are right, it wasn't required. Sajan, Shaggy mentioned that he wanted to exclude 0s and Blanks. So I thought that was a quick way to cover for that.
  19. Sam Mathai Chacko

    Minimum amount to be shown from multiple data sheets

    Enter as array formula =SUMPRODUCT(MIN(IF('Line data'!I5:I133*('Line data'!C5:C133='Line Data Weekly'!A6),'Line data'!I5:I133)))
  20. Sam Mathai Chacko

    Formula Challenge 022 - Find the item with the largest sum in a list

    Thanks Lori. I like that Sajan. I was sure the SUMIF could have been used better, but this is cool.
  21. Sam Mathai Chacko

    Formula Challenge 022 - Find the item with the largest sum in a list

    Interesting find Narayan. Made me add an IFERROR() to my original mega formula (which by the way still falls under Lori's radar of row location, but is still effective) ;)...
  22. Sam Mathai Chacko

    Formula Challenge 022 - Find the item with the largest sum in a list

    :) IFERROR(someerror,) returns 0 =LOOKUP(1,1/FREQUENCY(-9^9,MMULT(N(TRANSPOSE(LOOKUP(ROW(X),ROW(X)/ISERR(-X),X))=X),IFERROR(-X,))),X)
  23. Sam Mathai Chacko

    Formula Challenge 022 - Find the item with the largest sum in a list

    Lori, yes I agree about the row location. But thanks a lot for that innovative use of FREQUENCY. That would have never occured to me! Sajan, here's a shorter version of your formula =LOOKUP(1,1/FREQUENCY(-9^9,-MMULT((TRANSPOSE(LOOKUP(ROW(X),ROW(X)/ISERR(-X),X))=X)*N(TRANSPOSE(X)),ROW(X)^0)),X)
  24. Sam Mathai Chacko

    Formula Challenge 022 - Find the item with the largest sum in a list

    Abhijeet, basically, this is what my formula is doing =INDEX(X,INDEX({1,4,8,11},MATCH(MAX(SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>")),SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>"),))) I'm sure you can make out what's happening in there now. :)
  25. Sam Mathai Chacko

    Divide number in column B by number in column C if they in the same category

    Just to align to your requirement, modifying Abhijeet's suggestion slightly, =IF(B2=SUMIF($A$2:$A$25,A2,$B$2:$B$25),SUMIF($A$2:$A$25,A2,$B$2:$B$25)/SUMIF($A$2:$A$25,A2,$C$2:$C$25),"")
Top