Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stumped by a question (Excel 97+)

    I have to take this basic proficiency test and I have been asked to do this basic task: copy this area and paste it elsewhere so that it changes when the original values change...or something quite close to that. I screw it up and have to move on. My question: is there some new paste special or other command in post '97 Excel that enables such a setup. I would think they want a paste special/formulas, but that command does not relate back to the original data. I may have read the question incorrectly, and I can't sit there and copy it verbatim from the screen, but I thought I'd ask this Forum whether there is something new that does this. I do fine on the test anyway, I just hate it when I don't ace these simple questions. Thanks alot for any comments!

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

    Re: Stumped by a question (Excel 97+)

    The Paste Special... dialog has a Paste Link button. This will paste the copied area as a series of formulas referring to the original area.

    Note: you must leave everything else in the Paste Special dialog untouched; otherwise the Paste Link button will be greyed out.

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

    Re: Stumped by a question (Excel 97+)

    I do not know of any way to do what you described using copy/paste.

    The best way I know of is to use formulas in the "copy" area that reference the "copied area". For example, if you want a cell on Sheet2 to have the same value as cell A1 on Sheet1, then you would put this formula in the cell on Sheet2:

    <pre>=Sheet1!A1
    </pre>


    You could also do it using VBA.
    Legare Coleman

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

    Re: Stumped by a question (Excel 97+)

    Nice! I had never noticed that button before.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stumped by a question (Excel 97+)

    Thanks! I just noticed that paste link button myself. But if Mr. Coleman had not noticed it either, I'm in good company!

  6. #6
    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: Stumped by a question (Excel 97+)

    Another way is to use the CAMERA. This creates a "picture" of a section of the spreadsheet including all the formatting.
    It is great for summary tables to display, and to pull and print multiple selections from various worksheets.

    It is also a great way to add a datatable to a chart that is live.

    You can even link to rangenames which allow the size of the picture and location to be dynamically linked to pulldowns.

    I added it to my toolbar. When you View - toolbars - customize - command tab it is in the "Tools" category near the bottom.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stumped by a question (Excel 97+)

    How 'bout that! Never heard of it, but there it was. Thanks. Would you elaborate on the third paragraph, please? I'm getting funny formats right now, but that will change with familiarity. I just finished an exercise example on dynamic range names for charts, so I think I'll be able to pickup what you describe, or do an attachment.

  8. #8
    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: Stumped by a question (Excel 97+)

    I don't know how much elaboration you need. If you need more, be more specific on your needs and I will create a better example:

    The easiest way to make dynamic range names is using OFFSET.
    Define a name , eg named CameraRegion, as =OFFSET(reference,rows, cols,height,width)
    Where reference,rows, cols,height,width, refer to variables which define your region (see HELP for more info). If these variables or range references are set up to change, CameraRegion will also change. The variables could change because they refer to references for comboboxes, they refer to calculated cells, or any other region.

    Once you create your "picture" with the camera, select the picture, and you will see that the formula bar gives the address that pictures refers to, change it to =CameraRegion, and when the range CameraRegion changes size, the picture will change size.

    Hope this helps,
    Steve

  9. #9
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Stumped by a question (Excel 97+)

    Another way to paste the values as a link...

    Make sure the area is highlighted... click and hold on the border with your RIGHT mouse button and drag and drop to the area you want. When you release the mouse button a menu will pop up with the option to "Link Here." Using that option will refer to the original cell addresses.

    Dennis

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stumped by a question (Excel 97+)

    Very neat Dennis, never seen that one before.

Posting Permissions

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