+ Reply to Thread
Results 1 to 4 of 4

Change Referenced Cell Value Without Changing Formula

  1. #1
    Registered User
    Join Date
    06-07-2011
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    56

    Change Referenced Cell Value Without Changing Formula

    Hi Guys,

    So I have some cells that have a formula to lookup an label. I'm using the vlookup function to get the proper label. Here is my issue. The label pops up and says something like this

    "Combine [Part] and [Part]"

    I want it so the user can click the cell and edit the label to be what they want such as

    "Combine Hook and Loop"

    However when I click on the cell, the formula pops up. Is there a way to have it so when you click on the cell, the value pops up instead of the formula?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Change Referenced Cell Value Without Changing Formula

    This description is a bit confusing, can you attach your workbook?

    What do you mean by "label"?
    What causes it to "pop up"?
    What do you mean by "pop up"?
    Do you want to edit the cell containing the VLOOKUP (a guess on my part) formula, or the cell that it found?
    When you click on the cell, what formula "pops up"? Normally when you click on a cell nothing "pops up".

    Are you saying that the VLOOKUP formula shows a value, and you want the user to be able to edit that value, in the cell where it is contained? By somehow clicking on the cell that has the VLOOKUP formula?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-07-2011
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: Change Referenced Cell Value Without Changing Formula

    Sorry if that was confusing. If you check my attached sample you can see what I mean.

    If you go to edit what appears in the Label column, it takes you to the formula instead of the actual value that appears in the label. Is there a way to not have the formula pop up, but rather edit the value?

    I know there are other ways to structure this than vlookup, but I have to use vlookup because in my real example, the lookup is a bit more complex than this simple example.
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Change Referenced Cell Value Without Changing Formula

    Well, two months later I went back and looked at some of my loose ends and found this.

    The only way to do what you want is to use a macro. I have attached a version of your workbook with a macro in it. Whenever you click on a cell in the worksheet, if it has a formula the macro changes it to the value as displayed. Then you can edit it. But if the lookup value changes, the cell won't change because it no longer has a formula.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1