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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 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.

  3. #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.

  4. #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
  •