Posts by darkyam

  • Stop Formula Column Reference Changing On Insert But Not Row Reference

    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.

  • Copy Conditional Formatting Between Ranges

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

  • Remove Character From Range & Pad Resulting Numbers With Zeros

    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.

  • Formula Reference To Cell On Another Sheet

    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.

  • Vba Book Suggestion

    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.

  • Concatenate Cells Into 1 & Align Under Multiple Headings In Single Cell

    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

  • Summary Count Of List Items

    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.

  • Conditional Formatting Lookup To Highlight 1st Match Only

    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.

  • Conditional Formatting Lookup To Highlight 1st Match Only

    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.

  • Highlight Duplicate Cells Between 2 Columns

    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.

  • Conditional Formatting Lookup To Highlight 1st Match Only

    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.