Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Format cell ref with number format (Excel xp)

    I have a range of cells that reference a sheet called "base".

    =base!B18 & " " & base!C18

    When i change range B18 on the "base" sheet to 1 decimal place the resulting range on the other sheet "labels" does not change. Can i change the format in the formula to "match" the one on the base sheet? I will either be 0, 1 or 2 decimal places. Thank you.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cell ref with number format (Excel xp)

    That is only possible using either a macro or using heavy trickery involving XL4 macro functions inside Defined names. Look at my argname.zip from the Excel MVP page below, it contains an example that adds numbers as they are shown in the cell.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format cell ref with number format (Excel xp)

    ok, i looked at the zip file but it is over my head. can u get me started on a macro solution? thank you

  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: Format cell ref with number format (Excel xp)

    Create the function in VB:
    <pre>Option Explicit
    Function CellDisplay(rng As Range)
    CellDisplay = rng.Text
    End Function
    </pre>


    Then instead of:
    <pre>=base!B18 & " " & base!C18</pre>


    Use:
    <pre>=CellDisplay(base!B18)&" "&CellDisplay(base!C18)</pre>


    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
  •