Re: Currency Divided By Time
Chew,
Please cross post, as you agreed to do in the rules. http://excelforum.com/showthread.php?t=636196
Re: Currency Divided By Time
Chew,
Please cross post, as you agreed to do in the rules. http://excelforum.com/showthread.php?t=636196
Re: Conditional Formatting Lookup To Highlight 1st Match Only
Nice. Thanks for posting the solution for our edification.
Re: Change Value Depending On Column Header
I'm not good enough with VBA to help in that regard, but as a formula option, if you hid column C and put this formula in D4 and copied down, it would give the results you wanted. =IF(C4=0,"",IF(OR(LEFT(A3,6)="WITHDR",D3<0),-1,1)*C4)
Re: Stop Column Reference Changing On Insert
Indirect is not necessary, nor are the quotation marks. What you need is to put $ in front of the column letters (e.g., Attendance!$C9:$Z9) to keep them from changing when you drag the formula and the row will change automatically when you drag the related formula down. This would require lines to be added in the same place on both sheets to keep the lists in order. An alternative to this that only requires that the sheets have the same number of lines is to use =OFFSET(Attendance!$C$9,Row()-9,0,1,24) as the range in your formulas.
Re: 2007 - Copying Conditional Formatting`
Two ways: Copy--> Paste Special --> Formatting or go to the Conditional Formatting menu and adjust the range in the "Applies to" box for that rule and click Apply. Make sure your rule does not have absolute references (i.e. no $ in the formula).
Re: Remove Character From Range & Pad Resulting Numbers With Zeros
If you have a little leeway with the format, you can use the find and replace on the raw data, then have a helper column that is =B1&Rept(0,8-Len(B1). You would then be able to hide B, but still run all formulas you want off of it. You would see the data as you want in the helper column and it would not be dependent on column sizing.
Re: Vba Book Suggestion
Both the book I suggested and StephenR's have a 2003 version.
Re: Plus Or Minus If Statement
=if(or(b2-a2>500000,b2-a2<-500000),b2-a2,0)
Re: Formula To Refernce The Text In Another Cell
If the format is roughly the same (i.e., a building is listed on every other line in both sheets) you can just do, =Sheet1!A1 in Sheet2, cell C4 and copy and paste on every other line. For a draggable formula, =IF(Sheet1!A1="","",Sheet1!A1) and drag down. This only works if the line spacing is the same on both sheets.
Re: Vba Book Suggestion
I've been going through Excel 2007 Power Programming with VBA by John Walkenbach. It starts out with the basics of VBA, but covers a lot of ground. It also has several appendices at the back that make a good reference and a CD with workbooks showing the code in the book at work. There's a version for Excel 2003 as well.
Re: Concatenate Cells Into 1 & Align Under Multiple Headings In Single Cell
Aside from Dave's valid point, part of the problem is that you are not using a fixed-length font (i.e., all characters take up the same amount of space). Switching to Courier, or some other fixed-length font, and changing your formula to =A4&REPT(" ",10-LEN(A4))&I4&REPT(" ",10-LEN(I4))&J4&REPT(" ",10-LEN(J4))&K4&REPT(" ",10-LEN(K4))&L4&REPT(" ",10-LEN(L4))&G4 should yield the results you want. You can lower the 10 to decrease the space between the numbers. Please note that this produces alignment on the left side of the numbers.
To align on the right side, you would use =A4&REPT(" ",10-LEN(I4))&I4&REPT(" ",10-LEN(J4))&J4&REPT(" ",10-LEN(K4))&K4&REPT(" ",10-LEN(L4))&L4&REPT(" ",10-LEN(G4))&G4
Re: Summary Count Of List Items
You should be able to shorten the run time of StephenR's formula by changing it to =IF(A2=A1,C1,COUNTIF($A$2:$A$10,A2) and dragging down. That way, Excel only runs the COUNTIF the first time it sees each time rather than on every line.
Also, as a general rule, using built-in functions, if possible, is nearly always faster than even the best-designed macro.
Re: Conditional Formatting Lookup To Highlight 1st Match Only
Will the cell you want formatted always be the first cell after the two cells that add up to the amount in column A? If so, the format could be =IF(C28+D28=$A28) in E28 and then just copied over and down. If the percentages are always going to be 51% and 49% and the cell after the 49% should be highlighted (considering that A is multiplied by 49% to get the value in your table), you could also do =IF(C28=$A28*.49) in D28 and copied over. I'm honestly not sure how to get it to go strictly off the product table, but all five of your desired results are for the first cell after A*.49 = the preceding cell.
Re: Conditional Formatting, Halt Formatting After First Value
If the values to the left are what you want to check if they're 0, then you can still combine your formula with mine, just replacing the part that refers to the value in the cell itself with your Vlookup formula. If I'm not understanding this quite right yet, could you please post a watered down spreadsheet? Thanks.
Re: Calculate The Difference Between Two Work Days
I tried it with his daddylongleg's formula and it worked fine for me. From your example data, 3/3/08 is a Monday, so delivering on 3/2/08 is still technically 0 work days early.
Re: Highlight Duplicate Cells Between 2 Columns
If the lists are of unequal length, change TheDude's formula to =IF(ISERROR(VLOOKUP(A1,B$1:B$345,1,False)),"Delete",0). Conditional formatting could be =IF(ISERROR(VLOOKUP(A1,B$1:B$345,1,FALSE))=FALSE,TRUE,FALSE), formatted however you want.
Re: Conditional Formatting, Halt Formatting After First Value
It's unusual to have a VLOOKUP in a conditional formatting formula, especially if the same value that formula would return is in the cell itself. Why not just have the formula refer to the cell's value? If your data begins in column L, then the formatting formula for that cell could be =L242<>0 and for column M, change it to =AND(SUM($L242:L242)=0,M242<>0) and copy the format to the right.
Re: Counting Records Based On Dates
Assuming your data is in cells A2:C4 (headers in row 1), for 30 days, =SUM((A2:A4<=Today()-30)*(B2:B4="Fixed")*1) and confirm with Control+Shift+Enter. You could also use the similar formula SUMPRODUCT. Welcome to the forum.
Re: Calculate The Difference Between Two Work Days
Why not just subtract 1 at the end of the formula?