How do you refer to a dynamic named range?

[Ian Staines]

I have a dynamic named range in a spreadsheet called myRange. The
position of this range in the spreadsheet may change.

How can I refer to the contents of this range named "location" in
another spreadsheet?

Example:

myRange=Data!A1:B2
value1 value2
value3 value4

In a new location, say at Results!B2:C4 I want
value1 value2
value3 value4

Under the condition that next time the range could be
myRange=Data!A5:B7 for example.

I tried cutting the range and paste/special, but it always refers to
the range as cell references in the new sheet.

Ian Staines

 

[Tom Ogilvy]

assume I have a named range in an open workbook named Check_Proc.xls. The
range is named myRange and it is a 4 cell square

Pick a 4 cell square in another open workbook and enter

=check_proc.xls!myRange

in the formula bar. Enter with Ctrl+Shift+Enter rather than just enter
since this is an array formula.

You should then see the values from the named range myRange.

If you meant in code, since you posted in programming

Dim rng as Range
set rng = workbooks("Check_proc.xls").Names("myRange").ReferstoRange

 

[Ian Staines]

Thanks Tom

I had not thought about using arrays. This seems to be a good solution.
I am still experimenting with what happens when size of the original named
range changes with inserts and deletes.

I also tried using a series of references using offsets,
=OFFSET(myRange,0,0), but you have to set up each target cell individually.

Thanks

Ian Staines

 

[Tom Ogilvy]

Not necessarily.

In the upper left corner:

=OFFSET(check_proc.xls!myRange,Row(A1)-1,Column(A1)-1)

then drag fill right and down.