Search results for query: *

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 + ...

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

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

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

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

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

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.

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).

...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=""))+...

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

...add them up for the week and display a result like 41:30 using Custom format [h]:mm In such a case, you would calculate "days" as (total time) * 3 and display it as a number using format #.00 If you store time on your timesheet using whole numbers for each hour (e.g. arrive at 8, leave at...

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

...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 DETAIL'!$H$5:$H$4999<>"T702")*(('DISPUTE...

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

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

...Name],"Bob",Table1[Issue Code],{"X","Y"})) In Excel 2003 or earlier, you can use SUMPRODUCT: =SUMPRODUCT(C$2:C$350,(A$2:A$350="Bob")*(ISNUMBER(FIND(B$2:B$350,"XY")))) The reference to row 350 is arbitrary and may extend beyond your data. Sample file in my SkyDrive Brad

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

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

...DateFinder = "" Set RegExp = CreateObject("VBScript.RegExp") With RegExp .Pattern = "(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)([a-z]*)(\s*)(\d{2,4})" .Global = True .IgnoreCase = True If .test(sInputText) Then Set oMatches = .Execute(sInputText) If...

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