• 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

    Mental Block - Excel Formula

    Then you can use this array formula : =SUMPRODUCT(--($A$2:$A$11="Audi")*($B$2:$B$11<>"")) and confirm with ctrl+shift+enter
  2. V

    Mental Block - Excel Formula

    Hi nycguy, Assuming your data of car name is in column A (A2:A11), and sell price is in column B (B2:B11), formula to achieve your result for Audi car is: =SUMIF($A$2:$A$11,"Audi",$B$2:$B$11). Just change the car name in the formula to Buick or BMW instead of "Audi"
  3. V

    Please help me get the formulas to find out my desired shipping date. [SOLVED]

    Hi Ryan, Say your delivery date is in cell A2, Formula for closing date would be: =IF(WEEKDAY(A2,2)>3,A2+10-WEEKDAY(A2,2),IF(WEEKDAY(A2,2)<4,A2+3-WEEKDAY(A2,2),"")) and formula for ETD would be: =B2+3 There could be a better way of doing this, but, hope this helps.
  4. V

    VLOOKUP formula doesn't return data in cells when it should

    Hi Andrew, When clicking the F2 in the affected cells doesn't clear up the issue, You can perhaps 'Clear Formats' in the affected cells and click F2. I have seen this issue occurs for some of the BW or SAP downalods.
  5. V

    Not to allow copy hidden rows columns

    Just a thought: Keep your pricing calculations in a seperate sheet. Go to VBA screen (Alt+F11). In the properties of the worksheet where pricing calculations are available, change the visible property to "2 - xlSheetVeryHidden" from "-1 - xlSheetVisible". You can even password protect the VBA...
  6. V

    find the max date

    Hi coolcarnee, Say your data is from A1:B9, your formula in C1 would be : =LOOKUP(A1,$A$1:$A$9,$B$1:$B$9) or simply =LOOKUP(A1,$A$1:$B$9) Filldown the formula and you would see your result. To acheive this you need to sort your Dates in ascending order. Similarly if you want to get min...
  7. V

    Pivot table 2003 - 2007

    Hello Indian, Page field in xl 2003 is same as Report filter in xl 2007. Hope this helps.
  8. V

    Date

    Try this : =EDATE(A1,-12)
  9. V

    Error, what is the reason?

    Hi GuityN, x and i are declared as strings. But they are used as numerics. That might be the error. Change x and i as integers. Hope this helps.
  10. V

    Copying one range to another vba

    I faced this problem sometime ago when I was writing code for someone. When we hardcode the range like Range("A1:B3") it doesn't throw up the error. But if you want to refer the range using cells like Range(Cells(1, 1), Cells(sourceh, sourcew))then it throws up this error (Appliaction...
  11. V

    Copying one range to another vba

    Hi dan_I, Try selecting Sheets("test") before equalizing the range. Something like this. Dim sourceh As Integer Dim sourcew As Integer sourceh = Range("chartsource").Rows.Count sourcew = Range("chartsource").Columns.Count Sheets("test").Select Sheets("test").Range(Cells(1, 1)...
  12. V

    Total Time taken

    Hi Srinivas, This is little long..but works. =NETWORKDAYS(A1,B1)*9-IF(HOUR(A1)<8,0,(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))-TIME(8,0,0))*24)+IF(HOUR(B1)>17,0,(TIME(HOUR(B1),MINUTE(B1),SECOND(B1))-TIME(17,0,0))*24) Change the format of the cell to general to see the time difference in...
  13. V

    Conditional Formattin

    Hi niting, Select your range. In conditional formatting, rule type: Use a formula to determine which cells to format. formula is : =AND($A1=0,$B1=0) Go ahead and format the cells as you like. Hope this helps.
  14. V

    Challenging Custom Format or Conditional Format

    Hi, I think this would work. Paste this in the custome format. [<=100]#"%";# Hope this helps. Cheers VaraK
  15. V

    Very basic formula question

    Hi, Did you try conditional formatting? The formula you can use for what you are trying to do is (What I'm doing with the formula is if the due date was yesterday and the complete date is empty, then mark the entire row ): =AND($G10=TODAY()-1, $U10="") You can apply this conditional...
  16. V

    Pasting pictures

    Hi Don, Also, try capturing the screenshot using Alt+PrintScrn. This only captures the active application.
  17. V

    how to insert multipul row Between 10 selected row

    Hi SALSINHO, Try this code in the sheet module: 'Sub insertCol() Dim lng As Long 'Counts the number of active columns in row 1 lng = Cells(1, Columns.Count).End(xlToLeft).Column 'Inserts rows between columns For lng = lng To 2 Step -1 Columns(lng).Insert Shift:=xlToRight Next End...
  18. V

    Sumproduct at each change in voucher no

    Hi Nikhil, From what I understood from your post, I think Subtotals would be one option you can look into.
  19. V

    How to get defined format to carry over into CONCATENATE function

    Perhaps: =CONCATENATE("The number: ",TEXT(A1,"0.0.0")) Hope this helps.
  20. V

    How to detect formulas in cells (without VBA)

    Hi Cyril, I like this question. Certainly, VBA has an option: Range.HasFormula. I'll add this to my favorites to see the answer, if any.
  21. V

    simple calculation multiple steps and is difficult to explain

    Hi, Try this: =IF(A1<B1,0,IF(AND(A1>=B1,A1<B2),A1*5,A1*5.25)) Hope this helps.
  22. V

    Help wirting this ARRAY (1951 1952 1953........2008)

    I thought, I'll share my thoughts too. I guess there is no need for hardcoding of years in vba code or in a seperate sheet. The below code searches for worksheet names from 1951 to current year and runs your code. Hope this helps. Sub test() Dim i As Long, j As Long i = Year(Now()) ' Gets...
  23. V

    Joining two formulas

    Hi Kunal, My previous formula ="L"&RANK($G6,$G$6:$G$10,1) only ranks the numbers (Infact, Rank formula ranks the numbers :) ). It throws up #N/A if the cell is blank. Let me know if this is not the case. By using if and isna we can eliminate #N/A's and make that as invalid rank...
  24. V

    Ideas Pls in MIS

    How about building a forecast model,actual vs forecast variance and unit costing?
  25. V

    Joining two formulas

    Hi Kunal, Did you mean something like this? H6 is the rank you are calculating for G6 from G6:G10? If yes, then the below formula works...
Back
Top