Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I have an Excel spreadsheet with four columns (at the moment). The header is a drop-down selector, i.e. I am able to display/print a subset of data based on my selection. Now, what I can't work out (originally this table was a Word table & I could of course hide part of a cell) is how can hide part of a cell. I need the hidden part only for sorting the columns. A small sample attached.

    The reason for hiding part of a cell (column1) is that I do not need to display/print a subset of items but need it for sorting of data. That is, people may added more rows at the end and then sort the list based on column 1. Perhaps, there is better way of doing this rather than hiding part of a cell (it worked in Word but doesn't have to be same for Excel).

    [codebox]Col1 Col2 Col3 Col4
    AAA d2 d2 d2
    BBB d3 d3 d3
    BBB ABC d4 d4 d4
    BBB DEF d5 d5 d5
    BBB GHI d6 d6 d6
    CCC d7 d7 d7
    ZZZ d8 d8 d8[/codebox]

    So from the sample above, this is the output I would like to print (BBB ...' to be only used for sorting of the data based on column 1 - of course all cells to be visible when editing the spreadsheet)
    [codebox]Col1 Col2 Col3 Col4
    AAA d2 d2 d2
    BBB d3 d3 d3
    ABC d4 d4 d4
    DEF d5 d5 d5
    GHI d6 d6 d6
    CCC d7 d7 d7
    ZZZ d8 d8 d8[/codebox]
    Attached Files Attached Files
    Thanks.
    Robie

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't really hide part of a cell in Excel, but you can set the text colour for part of the cell value to the same as the background colour. See the attached version.
    This is rather tedious, however - the colour won't change automatically with the background colour of the cell.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Another option would be to use a separate column for sorting purposes; this column can be hidden.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789297' date='17-Aug-2009 13:33']Another option would be to use a separate column for sorting purposes; this column can be hidden.[/quote]

    Thanks for both responses Hans. Somehow, I always thought Excel was much powerful than Word.

    Interesting idea of using a separate column for sorting. How would I go about doing this? To me it seems both coumns (column 1 and this non-printing column) *must* have same content. So how can I have column look like this non-printing columns without the the *part duplicate* information? Also, I tried to add a column outside the printing area but adjacent to other columns but then when I select all five columns, it doesn't give me option to sort all columns (sort is greyed out) based on the new column.
    Thanks.
    Robie

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Each program has its own strengths.
    Excel is more powerful than Word when it comes to calculations, charts etc.
    But Word is more powerful than Excel for word processing.

    In the attached version, the column SortCol can be used to sort the table, but it won't be printed since it is outside the print area.
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789316' date='17-Aug-2009 15:13']Each program has its own strengths.
    Excel is more powerful than Word when it comes to calculations, charts etc.
    But Word is more powerful than Excel for word processing.

    In the attached version, the column SortCol can be used to sort the table, but it won't be printed since it is outside the print area.[/quote]

    Wow. That is just perfect. Hans.

    A big THANKS.
    Thanks.
    Robie

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='789296' date='17-Aug-2009 08:31']You can't really hide part of a cell in Excel, but you can set the text colour for part of the cell value to the same as the background colour. See the attached version.
    This is rather tedious, however - the colour won't change automatically with the background colour of the cell.[/quote]
    Hello Hans
    As an aside; is it possible to format part of a cell's contents using VBA code?
    Regards
    Don

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='790322' date='22-Aug-2009 20:47']is it possible to format part of a cell's contents using VBA code?[/quote]

    Yes.

    With the help of the macro recorder...

    The following snippet will change the 5th through 9th characters of the active cell (must be text and not a formula result) italic and red:
    [codebox]With ActiveCell.Characters(Start:=5, Length:=5).Font
    .Name = "Arial"
    .FontStyle = "Italic"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 3
    End With[/codebox]

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='mbarron' post='790323' date='22-Aug-2009 20:56']Yes.[/quote]
    Thank you Mike.
    Regards
    Don

Posting Permissions

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