Indirect Formula to A Named Range Different Workbook

[Herbiec09]

Good morning All,

I am struggling a bit with the indirect formula where I am trying to reference a named range in a different work sheet.

The formula is retrning #ref and the formula is:

INDEX(INDIRECT("Statements & Variance Model.xlsm!"&"Source_Data"),ROW()-5,COLUMN())

The named range is called "Source_Data" in the workbook called "Statements & Variance Model.xlsm". It seems that the part which is producing the #ref is : "Statements & Variance Model.xlsm!"&"Source_Data"

Can anyone kindly assist?

Thank you

Herbie

 

[James006]

Hello,

Are both workbooks opened ...or not ... ?

HTH

 

[Herbiec09]

Yes, both workbooks are open

 

[James006]

Fine ...

If The named range is called "Source_Data" in the workbook called "Statements & Variance Model.xlsm" ...

Could it be that you are doubling up the file name ...

By the way ...have you tested :

=INDEX('Statements & Variance Model.xlsm'!Source_Data,ROW()-5,COLUMN())

 

[James006]

Have you had a chance to test the proposed formula ...?