| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you refer to a dynamic named range?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do you refer to a dynamic named range?
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").Refer stoRange -- Regards, Tom Ogilvy Ian Staines wrote in message m... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do you refer to a dynamic named range?
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" wrote in message ... 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").Refer stoRange -- Regards, Tom Ogilvy Ian Staines wrote in message m... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do you refer to a dynamic named range?
Not necessarily.
In the upper left corner: =OFFSET(check_proc.xls!myRange,Row(A1)-1,Column(A1)-1) then drag fill right and down. -- Regards, Tom Ogilvy Ian Staines wrote in message ... 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" wrote in message ... 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").Refer stoRange -- Regards, Tom Ogilvy Ian Staines wrote in message m... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| make camera object refer to dynamic range | Excel Discussion (Misc queries) | |||
| Refer to a named range indirectly | Excel Worksheet Functions | |||
| Refer to Named Range on another sheet for IF function | Excel Worksheet Functions | |||
| ???Refer to a specifc cell in a named range | Excel Worksheet Functions | |||
| How does one refer to the n-1 element of a named range? | Excel Discussion (Misc queries) | |||
Hybrid Mode
