Problem linking to Named Ranges from another workbook

[NeverHappyMike]

Hi


I have many workbooks each with 5 specific worksheets (lots of people have their own workbook). I'm trying to collate the individual worksheets into 5 separate Master Workbooks (that I can work from). I'm having problems.


Within my colleagues workbooks I've assigned specific Named Ranges to the data area: $A$2:$I$43 is XXX1MyNamedRange. I thought I would then be able to refer to these ranges from my Master Workbooks (excel help, and common sense, says this would be possible).


My problem seems to be that unless the Master Workbook cell that I put the reference in is a cell that is included within the XXX1MyNamedRange it gives me #Value.


Example: Cell reference is in $A$2, $D4$, etc then it works. Celle reference is in $A$44 then I get value.


I'm trying to make a long list of the data: all the data is in the same format and covers the same rows and columns.


Any help, advice or better working practice for doing this would be appreciated (I am unable to export this data to an external database).


Many thanks

Michael.

 

[Hui]

Michael


Sounds like you need to use the Indirect function

Indirect will take text or cell references and make a formula out of them


so you can do something like


A1: Ganttsample.xlsm

A2: Calculs

A3: B

A4: 9


A5: =INDIRECT("[ & A1 & ]" & A2 & "!" & A3 & A4)


Which is the same as entering

=[GanttSample.xlsm]Calculs!$B$9


So you can setup a table of Workbook Names and Sheet Names and then use some formulas similar to above to link in and retrieve the other values.


You can hard code any piece of the equation inside the indirect so long as it is a text value

 

[NeverHappyMike]

Thanks Hui


I've never used INDIRECT before, so I'll give it a go.


Just to confirm, and I'm slightly confused:

[list type=decimal] I create a table in each Master workbook where,
[list type=decimal] $A$1 has Specific workbook name
$B$1 has specific sheet name[/list type=decimal]
Make the table as long as is required to capture all my colleagues workbboks and worksheets 95 per book)[/list type=decimal]

Do I then write the INDIRECT formula refering to the table references above and then adding on the Named Range used in my colleagues worksheets?


Unfortunately I don't see understand how the range from my colleagues worksheets will end up in my master workbook.


Thanks again

Mike.

 

[Hui]

You will need to have a list of Workbooks and Worksheet names down the side with a list of Cell References across the top

Then use an Indirect to join the combination of Workbooks, Works sheets and References to extract the cells from the appropriate workbook/worksheet/cell combinations

 

[kchiba]

Hi Mike,


Have you tried the Consolidate Function, make sure you tick the create links to source data.


cheers

Kanti

 

[cyrilz]

@Hui... Never thought I'd use INDIRECT with that level of split.


@Mike My guess is you also need OFFSET function to select the data in the sheets. That way you can refer only to sheetNames, directly from the master workbook.


Another way is to use "external data" links in sheets in the Master Workbook. Then either use consolidate or free calculations...