I’m frustrated

How do I remove references to other cells while leaving content untouched?

I'm using Excel 2007's hyperlink function to grab a URL from cell A1, a friendly label from cell A2, and creating in cell A3 an active hyperlink with a friendly label. All good.

But what I need to do now is remove cells A1 and A2, leaving cell A3 intact.

But when I do that, I get a #REF! error. How do I disconnect A3 from A1 and A2, such that they can be deleted, whilst leaving my content in cell A3 functional?
1 person has
this question
+1
Reply
  • Hello Mark,

    I'm not an expert in this subject, but I did find a page online that hopefully you will find useful: http://office.microsoft.com/en-us/exc.... Hope this helps and have a great day!

    Kind regards,
    Paul Fichera
    Customer Service Representative
    O'Reilly Media
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Hello Mark,

    What you want to do is put all of the hyperlink information into cell A3, and the instructions on the page Paul refers you to will help you do that.

    Please let us know if you need any further help.

    Nan Barber
    Editor
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Thanks Paul and Nan,

    I have looked at that page and it doesn't answer my question.

    I suppose my question can be made more generic. Basically, when you have a cell's content dependent on the content of two other cells (addition or multiplication for example), is there a way of, having generated that content in the third cell, telling that third cell to retain that content, regardless of what happens in those two cells whose content was used to generate that content?

    Example: cell A1 contains "2", cell A2 contains "5", cell A3 contains "=SUM(A1,A2)" which displays as "7".

    Is there a way to remove that formula from cell A3 while leaving the content in the cell as "7"? (Apart from the obvious manual copy and paste solution.)

    If there was, I could then delete the content in cells A1 and A2 and still have "7" showing in cell A3.

    That's what I'm after, generically, although in my case I'm not dealing with the SUM function, but with the HYPERLINK function.

    I know what I'm asking is contrary to how you'd want a spreadsheet to behave almost always, but I'm doing some data cleansing/migration work and having this capability would be very handy.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Hi Mark,

    I understand what you're trying to do, but I'm not sure if it's possible.

    Here's the closest I could come up with:
    http://office.microsoft.com/en-us/exc...

    Are you using an O'Reilly book for help with Excel, too? If so, which one? I may be able to get some help from the author, too.

    Nan
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • I’m thankful!
    Thanks Nan - that is the solution I've been looking for! It works a treat!

    FYI, I'm using Excel 2007 The Missing Manual.

    Now that you've brought this solution to my attention, I'll probably find it in the book...

    Thanks again - much appreciated.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • I’m hopeful
    Aah... I spoke too soon.

    It works a treat with a simple addition or multiplication example (which is good to know about), but when it comes to the A3=hyperlink(A1,A2) example, while F2, F9 removes the reference to cells A1 and A2 and leaves the friendly link text in cell in A3, it is no longer a hyperlink...

    So if there is a solution which does what F2, F9 does but leaves A3 as a hyperlink, I'd love to know about it.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated