Pasting Dynamic named ranges into another workbook

[johnturgoose]

I have a dynamic named range CENTRES defined using the OFFSET function in workbook1

in workbook2 i want to paste a link to that range. However, obviously the CENTRES range is growing.

The simple solution is to paste a link to the cells containing the range and all those it will contain during the life of this particular spreadsheet. It will work but I kind of find it untidy to link to 2500 empty cells and obviously it will slow things down. Is there any way of pasting dynamically?

many thanks for any assistance

john

 

[SkipVought]

john,

Paste a link? Maybe a reference to the named range in one of the lookup function, but I would NOT recommend pasting the range as it is DYNAMIC.

How do you intend to use this range?

I might be apt, unless I'm guessing incorrectly, to use a simple parameter query from your new workbook to the workbook/sheet containing the named range. That might do the trick.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[johnturgoose]

Thanks Skip.

Perhaps paste a link is not accurate.

I have a range in WORKBOOK1. I have named the range CENTRES. The list is growing.

All i need to do is paste a copy of that range in WORKBOOK2. I want the copy in WORKBOOK2 to grow and match WORKBOOK1 as the CENTRES range grows.

Thanks

John

 

[SkipVought]

Why? It's already in Workbook 1. Why does it ALSO need to be in Workbook 2?

You can reference the range using lookup functions or you can query the range using MS Query via Data/get External Data/New Database Query -- Excel files -- Workbook 1 -- The Sheet where the dynamic range resides.

BTW, I you use the query approch, the range does not even need to be named. Just hast to have a Column Heading in row 1 for each column.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[SkipVought]

Why? It's already in Workbook 1. Why does it ALSO need to be in Workbook 2?

You can reference the range using lookup functions or you can query the range using MS Query via Data/get External Data/New Database Query -- Excel files -- Workbook 1 -- The Sheet where the dynamic range resides.

BTW, if you use the query approch, the range does not even need to be named. Just hast to have a Column Heading in row 1 for each column.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[johnturgoose]

Thanks Skip,

The reason for the duplication of data is:

Workbook1 is open most of the working day and is constantly been updated. Its massive and slow. Other people need only specific pieces of information from it to combine with their own work. Therefore the query option seems to work perfectly. With hindsight the whole thing would have been better produced as a database....

Thanks again
John