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

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

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

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

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

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

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

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

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

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

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