Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - linked formats (2000/sp3)

    Hello,

    Do you know how to get linked cells in the dependent worksheet/file to format the same way as the source sheet cells? Number formats for example - displaying negative #s with (parentheses) instead of (-) negative signs, zero value cells blank instead of showing a 0; color formatting.......

    thanks!!

  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: Excel - linked formats (2000/sp3)

    No direct way to do this.

    You can do it in a range by copy -paste-special formats

    If you only cared about the display "text" and not the tru value, you could write a function which converts the item to a string with the correct format:

    <pre>Function FormatMe(rng As Range) As String
    Dim rcell As Range
    Set rcell = rng.Cells(1, 1)
    FormatMe = Application.WorksheetFunction.Text(rcell, rcell.NumberFormat)
    Set rcell = Nothing
    End Function</pre>


    The main problem with this is that the TEXT does not always read the same as the custom excel format (it does seem to be better than VB's builtin FORMAT for most of them). It will also not do other types of formatting

    The other alternative is to use a macro, though it might be complex to determine which cells to act upon and copy the number formats and any other formats.

    Hope this helps,
    Steve

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - linked formats (2000/sp3)

    argh&$%#, nonetheless, thanks for your time steve.
    interesting approach - i'll look into it!

  4. #4
    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: Excel - linked formats (2000/sp3)

    If you need futher clarification, details, or want to discuss other approaches, don't hesitate to post again.

    Steve

Posting Permissions

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