Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. byundt

    Enter Date if cell is not blank

    Did you know there is a keyboard shortcut to enter today's date in a cell? The date will be entered as a static value, so need to turn iterative calculation on. Neither is there a need to format the cell as Date, because the shortcut does it for you. CTRL + ; shortcut for date CTRL + ...
  2. byundt

    How can I make Excel 2003 my default version?

    If you launch Excel first, then open the file, you will always get the desired version of Excel. If you double-click a file, it will open into whichever version of Excel is already open. Access and Word have the frustrating delay for registry keys when you launch them and have multiple versions...
  3. byundt

    Vlookup not working #REF error

    VLOOKUP has an optional fourth parameter. The default value (what you get if you don't specify a value) is TRUE, which means that Source column A is sorted in ascending order and an exact match is not required. If you require an exact match for cell A6, you will want to specify FALSE as that...
  4. byundt

    Left Padding data

    If you want to change the value being displayed but not the underlying data, consider formatting the cell using Custom format: 0000000000\,00 % The above Custom format has an ALT + Enter after \,00 and before %. You will also need to turn Wrap Text on and Align the cell contents to the top...
  5. byundt

    Left Padding data

    You can do it without need for SUBSTITUTE or Find & Replace using: =TEXT(100*A1,"0000000000\,00")
  6. byundt

    Sumproduct formula help needed

    In a SUMPRODUCT formula, you multiply criteria to get the equivalent of an AND on a row by row basis. =SUMPRODUCT((master!C3:C2002="Rx")*(master!R3:R2002="NA")) counts the rows where both criteria are satisfied Getting an OR is a little trickier. You need to add the criteria, then test...
  7. byundt

    extract string from right till the space

    If the cell text ends with one or more spaces, my formula will not give the correct answer. The remedy is another TRIM: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)) That formula will also work if the cell is blank, contains an empty string, or contains the Gettysburg Address.
  8. byundt

    extract string from right till the space

    As long as the last word contains fewer than 99 characters, you can return it with: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) This formula will work even if the cell contains a single word (i.e. no spaces).
  9. byundt

    IF AND OR COUNTIF help needed

    If you only want to count a row once (assuming the columns C and Q criteria are met), then consider the following formula: =SUMPRODUCT((Master!C2:C2001="Rx")*(Master!Q2:Q2001="NA")*(Master!N2:N2001=""))+...
  10. byundt

    Does Excel have an 'IN' ?!

    DeviousDi, Did you try the formula I suggested? Although shorter than your original formula, it adds the requested feature of counting only rows that contain neither S01, S02 nor S03, while still accounting for all the other criteria. I was able to make the revised formula shorter because I...
  11. byundt

    Excel formula help neededto convert hours worked to decimal days

    This is one of those problems where it would be extremely helpful if you posted a sample file. If you store time on your timesheet using a 24 hour clock (e.g. arrive at 8:00, leave at 17:00), then you subtract the one time from the other to get the hours worked on that day. You would then add...
  12. byundt

    match 2 colums

    Older versions of Excel do not permit you to use references to other worksheets in Conditional Formatting criteria. The workaround for that is to use a named range in place of the reference to the other worksheet. Step by step instructions: 1. On Sheet1, select column A 2. Click in the...
  13. byundt

    Does Excel have an 'IN' ?!

    If you only want to count rows where DISPUTE DETAIL column X is neither S01, S02 nor S03, I believe that your formula is equivalent to: =SUMPRODUCT(('DISPUTE DETAIL'!$X$5:$X$4999<>"S01")*('DISPUTE DETAIL'!$X$5:$X$4999<>"S02")*('DISPUTE DETAIL'!$X$5:$X$4999<>"S03")*( ('DISPUTE...
  14. byundt

    Excel 2010 - Find Bottom 10 Scores and Associated Names

    Try the following array-entered formula in cell C2 for the names. It may be copied down. =INDEX(A$2:A$20,MATCH(SMALL(B$2:B$20+ROW(B$2:B$20)/1048576,ROWS(C$2:C2)),B$2:B$20+ROW(B$2:B$20)/1048576,0)) To array-enter a formula, click in the formula bar, hold the Control and Shift keys down, then hit...
  15. byundt

    Office Pro 2003 new install-Product Key different than COA

    Daniel Petri gives instructions for changing the CD-Key associated with Office in this webpage: http://www.petri.co.il/change_office_xp_cd_key.htm# The setup.ini file may have a key preloaded in it. Daniel Petri describes how to do that on...
  16. byundt

    Adding Data Conditionally

    Assuming that you follow Skip's advice, SUMIFS will give you the sum of values for either Issue Code "X" or "Y". But you can use a SUM(SUMIFS(...)) formula to get the sum of both conditions: =SUM(SUMIFS(Table1[Value],Table1[Agent Name],"Bob",Table1[Issue Code],{"X","Y"})) In Excel 2003 or...
  17. byundt

    Conditional Formatting in Excel 2007

    I'm glad that Excel is behaving better today. Although Excel 2007 does have a number of reported issues with Conditional Formatting, I didn't see any problems like yours when I searched. Brad
  18. byundt

    Conditional Formatting in Excel 2007

    This is the type of problem that would be helpful if you answered a few questions: 1. Which version of Excel are you using? Bugs are often specific to a certain version of Excel. 2. Could you post a sample file that demonstrates the problem? Use fake data if necessary. 3. Is the code being run...
  19. byundt

    To ignore letters in cell during our calculations

    Using regular expressions, you can identify the dates in diabolical test strings and return Nov13/Dec13 for each case. The test strings below mix up month abbreviations with full text, spaces between month and year, and two & four digit years. Nov13/Dec13 176.00 +0.00 Correct Nov 13/Dec 2013...
  20. byundt

    To ignore letters in cell during our calculations

    Assuming the only two variants are as shown, you might search for the 5 characters before "/" and then the five characters before the subsequent space: =MID(A1,FIND("/",A1)-5,6) & MID(A1,FIND(" ",A1,FIND("/",A1))-5,5) If the data vendor is even more diabolical than shown, then you will probably...

Part and Inventory Search

Back
Top