Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Settiing 1 range equal to another (2000/2002)

    On worksheet 'A' I have defined a Named Range that consists of 2 cells. On worksheet 'B' I would like to set 2 cells equal to the Named Range I defined on worksheet 'A'. I have been unable to figure out how to do this. I did try giving the 2 cells on worksheet 'B' a named range but can not figure out how to set 1 range equal to another without using code.

    Thanks in advance!

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 78 Times in 74 Posts

    Re: Settiing 1 range equal to another (2000/2002)

    Hi Ed,
    If for example you defined a name "test" to refer to cells A1:A2 on sheet 1, then on sheet 2 you could select A1:A2 and type
    =test
    and then press Ctrl-Shift-Enter to array-enter it. The cells on sheet 2 would then be linked to those on sheet 1. Is that what you mean?
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Settiing 1 range equal to another (2000/2002)

    Close. What you suggested does work however ultimately I want my referencing range of cells to be dynamic to the referred range. EX: If the name on worksheet 'A' called 'Test' consists of the range A1:A3 and on worksheet 'B' I have a named range consisting of cells A1:A3 that have an array formula set equal to A!Test then if the range 'Test' on worksheet 'A' is increased by one row due to a row insertion I would like for the named range on worksheet 'B' to reflect the same.

  5. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Settiing 1 range equal to another (2000/2002)

    Try the following formula in worksheet B,

    =IF(ROW()<=ROWS(test),INDEX(test,ROW()),"")

    where 'test' is the name of the dynamic range on worksheet A.

    If the target range (worksheet [img]/forums/images/smilies/cool.gif[/img] does not start in row 1, you will need to adjust the formula thus
    =IF(ROW()-start<=ROWS(test),INDEX(test,ROW()-start),"") where start is the row() - 1of the first entry in worksheet B.
    Copy the formula down for as many rows as you think will ever appear in worksheet A.

    It is a bit of kludge but...

Posting Permissions

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