Results 1 to 9 of 9

Thread: Why Doesn

  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Seattle, Washington, USA
    Posts
    21
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Why Doesn

    If I have formatted specific cells as underlined and then sort the sheet, I want the underlining to follow the cells, relative to their new location.
    I don

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Why Doesn

    Instead of using the cell border to underline, use the underline button. This will get sorted with the cells.

    Steve

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Seattle, Washington, USA
    Posts
    21
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Why Doesn

    Using the underline button underlines data only. The cells I need to underline are blank. The user needs to print the sheets and have someone else manually write in information.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why Doesn

    You can use Conditional Formatting if there is some test that would identify that the cell needed to have the border.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Why Doesn

    If Legare's suggestion of conditional formatting is not applicable, you could add something into the cells that is unprintable (like spaces followed by a "sticky-space" (<alt>0160 on numeric keypad)

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why Doesn

    I would use a hidden column that contains a flag.
    Legare Coleman

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Location
    Seattle, Washington, USA
    Posts
    21
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Why Doesn

    I love a day when I learn something new! Here's what I did:

    Test worksheet contains 30 rows.
    Odd rows contain data in column B and even rows contain data in column C. Column A is currently empty.

    Where column C contains data, I want the D cell (D2, D4, D6, etc.) to be formatted with an underline. After this "checklist" is printed, the file will be deleted; no data will be entered into column D later.

    1. Add a sequence number in column A.

    2. Sort by column C.

    3. Select D1 and type a non-printable character <Alt/0160> (thank you, sdckapr).

    4. Select D1, grab the handle, and copy through all populated rows (row 30).

    5. Select D1
    Format
    Conditional Formatting...
    Cell Value Is equal to <Alt/0160>
    Format...
    (to taste)
    OK
    OK
    (thank you, Legare).

    6. Grab the handle of D1 and copy through all populated rows (row 30).

    7. Select the bottom cells with no data in column C and delete the data. This deletes the <Alt/0160> but not the conditional formatting.

    8. Sort by column A (sequence number).


    If column D were going to be updated later, and if I still wanted the cells underlined, I would use Legare's tip about a hidden column with a flag but I'm not sure how to associate another cell from within the conditional format.

    Thank you!

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why Doesn

    Lets say you were going to use column E for the flag.

    1- Select the cells where you want the conditional formatting (lets say D2 is the active cell and E2 controls the formatting for that cell). Then select Conditional formatting.

    2- Drop down the drop down list and change it from "Cell value is" to "Formula is".

    3- In the formula box enter:

    =$E2="y"

    4- Click the format button and set the formatting as desired.

    5- Click OK.

    Now, each cell in the selection will have the selected formatting if the corresponding cell in column E contains the letter y.

    If you have additional tests like column C not being empty, then you can either add and AND function function to the conditional formatting formula above, or you can put a formula in column E to control if it contains a "y" like this:

    <code>
    =IF($C2<>"","y","n")
    </code>
    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Nov 2001
    Location
    Seattle, Washington, USA
    Posts
    21
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Why Doesn

    Perfect!

    Thank you again . . .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •