Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding Part of a cell's contents

    Someone in the office swears he was shown a way -- of course he can't remember now -- of entering a value in a cell that contained a formula without losing the formula, but without the formula being calculated or visible in the cell (although it would be visible in the formula bar). That is, in a cell containing the formula =2*4, he is saying he could edit the cell to put the value 25 in front of the formula followed by a mystery character that would hide the formula in the cell so the cell would read 25, but the formula bar would read 25?=2*4. In addition, he says that the value of 25 in the cell would be a number, not text and thus be could be referenced in other formulas. I've never heard of this, but I'm willing to learn....

    Thanks!

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

    Re: Hiding Part of a cell's contents

    There may be another way, but here is the best way I could come up with:

    If the cell contains =2*4, then:

    1- Put a single quote in front of the Equal sign
    2- Press the End key to move the cursor to the end of the formula
    3- Hold the Alt key down and press Enter
    4- Type what you want to display
    5- Make sure that the cell height is only enough to display one line.
    Legare Coleman

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

    Re: Hiding Part of a cell's contents

    Actually, I just figured out a second way that may be a little closer to what you wanted:

    1- Select the cell
    2- Select Cells from the Format menu and click on the Alignment tab
    3- In the Vertical alignment drop down list select "Top" and click OK
    4- In the cell, type 25 in front of the equal sign and then hold down Alt and press Enter

    Very similarf to my previous post, but a little easier.
    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    Are you and your colleague sure that is not the other way round, i.e the Cell shows 25 =2*4 whilst the formula bar just shows 25. (That can be achieved by formatting the number as # "=2*4". If you enter 25 =2*4 into a cell it cannot evaluate to 25 except by using something like =LEFT(x,2) where x is the cell containing the expression.

    Maybe there is a character that tells Excel to disregard anything that follows it, but what exactly would be the point?. Unless it could be a way of inserting a comment, before the introduction of comments as we now know them. Perhaps a remnant from Lotus 123 for DOS!. If it can be done then somebody must know.


    Andrew C

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    My coworker insists he simply typed some kind of character as a separator between the the number and the formula and that formula would be hidden in the cell and the number would still evaluated as a number. Although Legare's suggestion can be used to display the number while hiding the formula, the number is not evaluated as a number.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    Well we should find this hidden character, it would be a nice way of putting a reminder into a cell. I am sure that if it does exist somebody will let us know.

    AC

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    Are you sure your collegue was talking about Excel and not Quattro Pro? In QP you can enter a semicolon after a value in a cell then enter something else and only the value will be visible in the cell but everything will be visible in the formula bar.

  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    No, it was Excel. We've never used Quattro Pro.

  9. #9
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    It seems if you enter:

    =2 + n("1+1")
    in a cell all that will appear in the cell will be the 2, everything will be visible in the formula bar and the cell will still be accepted as a number, capable of being used in formulas. It's pretty neat.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    N is a function which returns a value for numerical data and 0 for text data. So therefore N("1+1") = 0, which makes 2 + N("1+1") = 2 (i.e. 2 + 0). If you rfemove the parentheses from "1+1", the expression returns a value of 4. So while it may look interesting it is effectively the same as entering = 2 + 0, hence it does not really qualify.

    Andrew C

  11. #11
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    Actually this does accomplish what my coworker was looking for, being able to preserve an existing formula in a cell without it being calculated while at the same time plugging in a different value that could be used in other calculations that referenced the cell. By using the N function you can retain the text of the original formula for posterity, and play some what-if scenarios with other values. Good job!

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    I understood that a mystery character was involved that allowed the following to exist in a cell and return a numerical value : - 25? = 2*4 where ? is a mystery character - not quiet the same as the N() function, which incidentally is calculated in the above formula, it just happens to evaluate to 0. Change the + sign to * and you will see what I mean. =25 + 0*(2*4) achieves much the same thing. Pity there is no mystery character [img]/w3timages/icons/sad.gif[/img]

    AC

  13. #13
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Part of a cell's contents

    Sorry about the mystery character turning out to be a dry hole, but I was going on someone's else vague memory.

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Dry Hole

    No problem Tim, there is always something to be learnt even from a dry hole as you put it. It was worth the excursion - at least the N() function has a use after all (though the same effect could be achieved without it). Now can you come up with a good use for the T() function ? [img]/w3timages/icons/dizzy.gif[/img]

    regards,

    Andrew C

Posting Permissions

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