Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2007
    Location
    Tulsa
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    Is it possible to link cells and maintain the source cell's formatting?

    Thanks,
    JG

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    Welcome to Woody's Lounge!

    You can do the following:
    - Select a cell or range of cells.
    - Copy to the clipboard.
    - Select the destination cell.
    - Hold down the Shift key while selecting the Edit menu.
    - Select "Paste Picture Link" from the menu (this option is only available if you held down Shift as you selected Edit).

    You should be aware that this pastes a picture of the source cell(s) instead of pasting cells. You cannot use it in formulas etc.

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

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    You can copy the source cell, and then do a Paste Special, and paste the source cells format into the linked cell. However, if the source cell's format changes you will have to repeate this process. I do not know of any way to link cell's formats dynamicly. There is no event triggered when a cells format is changed, so there is not a way to do it with VBA.

    Also, note that, with Hans' solution, if the value in a source cell changes, the picture will not be updated.
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    If you use Edit | Paste Picture Link, the picture will reflect changes in both the value and the formatting of the source cell(s).

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

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    That does not appear to be an option with XL2K.
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    As far as I know, it has been available at least since Excel 95 - but, as noted, you *must* hold down Shift as you select the Edit menu, otherwise this option is not available.
    You can also use the Camera tool. It is not present on any toolbar by default, but you can put it on a toolbar using Tools | Customize...; the Camera tool is listed in the Tools category.

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

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    I did finally find the Paste Picture Link, and it does work as advertised. However, I can't get it to work in the same workbook where I copied the picture. If I try to paste the link into another worksheet in the same workbook, that command is grayed out on the menu. That is what I was trying to do earlier.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    For me (in Excel 2002) it works in the same worksheet, in another worksheet in the same workbook, and in another workbook.

  9. #9
    New Lounger
    Join Date
    Apr 2007
    Location
    Tulsa
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    Thanks for your help. That is not exactly what I needed, but it is an interesting trick. I have actually thought of another use for your tip. I was also very surprised to learn that holding the shift key changed the edit menu. That doesn't appear to have an effect on the other menus. Why doesn't "Paste Picture" just show up in the normal menu? Are there any other similarly hidden menus?

    Thanks again,

    JG

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link Cells -- Keep Source Cell's Formatting (2002 SP3)

    Microsoft apparently wanted to keep the number of menu items down, so they decided to make some items available only if you hold down the Shift key.
    The menu items affected are:

    File | Close becomes File | Close All (close all open workbooks).
    Edit | Copy becomes Edit | Copy Picture.
    Edit | Paste becomes Edit | Paste Picture.
    Edit | Paste Special... becomes Edit | Paste Picture Link.

    There are also some toolbar buttons that change their meaning if you hold down the Shift key while clicking them:

    Open becomes Save, and Save becomes Open.
    Print becomes Print Preview, and vice versa.
    Sort Ascending becomes Sort Descending, and vice versa.
    Align Left becomes Align Right, and vice versa.
    Align Centered becomes Merge and Center, and vice versa.
    Increase Decimal becomes Decrease Decimal, and vice versa.
    Increase Indent becomes Decrease Indent, and vice versa.

    There are a few more.

Posting Permissions

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