Settiing 1 range equal to another (2000/2002)

[edcarden]

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!

[rory]

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?

[edcarden]

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.

[kieran]

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