Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Name range defined in seperate spreadsheet (2003)

    Is is possible to use a named range from another work sheet in a separate spreadsheet instead of having to type out the values again?
    How do l go about calling the name range from a separate worksheet if this is possible, see screenshot for the named range which l need to use that is defined in a separate spreadssheet
    Attached Images Attached Images

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Using Name range defined in seperate spreadshe

    you can often use the Insert, Name, Paste menu to paste range names into dialogue boxes etc.

    Handy for avoiding retyping.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Name range defined in seperate spreadshe

    Hello,

    This did not work , l was not able to paste a named range from one workbook to another , can you explain how l paste the named ranges from one workbook to another please. A screenshot would be appreciated. Is this possible what l am trying to do?
    I could not see the option to copy the named ranges?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Name range defined in seperate spreadshe

    It is possible to refer to named ranges in one workbook from another workbook - use this syntax:
    <code>=BookName.xls!RangeName</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Name range defined in seperate spreadshe

    That

  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: Using Name range defined in seperate spreadsheet (2003)

    The named ranges in a workbook are valid on all sheets: you do not have to recreate them. (unless you make them local, which means they are only valid on that sheet).

    Named ranges to other workbooks can be created by making links to them. The refers to will be something like:

    ='C:Path[Filename.xls]SheetName'!RangeName

    The easiest way to create the link is to give it a name in the insert-name dialog, highlight the "refers to", then seelct the range on the other sheet or book.

    Steve

  7. #7
    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: Using Name range defined in seperate spreadshe

    You can go into a cell and enter an equal, then point to the range and the syntax will be created for you...

    Steve

  8. #8
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Name range defined in seperate spreadsheet (2003)

    That

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Name range defined in seperate spreadsheet (2003)

    And let me add that I have a free tool on my site to manage defined names:

    http://www.jkp-ads.com/officemarketplacenm-en.asp
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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