Formulas with Names in another Excel Spreadsheet

I am using formulas with names in another Excel 2007 spreadsheet, like this:

=AVERAGEIFS('Data.xlsx'!MIPS,'Data.xlsx'!YEAR,$A78,'Data.xlsx'!MONTH,$B78,'Data.xlsx'!PERIOD,$A$48,'Data.xlsx'!SERIAL,$G$48,'Data.xlsx'!SYSTEM,G$49)

However it is only working when the input DATA.xls spreadsheet is opened. When I close it, it is not recognizing the Names defined in the Data Spreadsheet.

I receive the following error when calculating the formula with the input file closed:
Microsoft Office Excel cannot find 'MIPS' on 'Data.xlsx'. There are two possible reasons:
The name you specified may not be defined.
The name you specified is defined as something other than a rectangular cell reference.
Check the name and try again.

The above error occurs for all defined names.
The names are all correct, as I said the formula is working only when the input DATA is opened.

Can anyone help me please?

Thanks!

Hi Trizzo,

Try inputting complete file path in the formula... thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

The complete path is also not working. When I put the full path name it does not recognize the Names inside it. Here is the same formula above with full path which is not working:

=AVERAGEIFS('C:\Documents and Settings\Administrator\Desktop\MAIN\Data.xlsx'!MIPS,'C:\Documents and Settings\Administrator\Desktop\MAIN\Data.xlsx'!YEAR,$A78,'C:\Documents and Settings\Administrator\Desktop\MAIN\Data.xlsx'!MONTH,$B78,'C:\Documents and Settings\Administrator\Desktop\MAIN\Data.xlsx'!PERIOD,$A$48,'C:\Documents and Settings\Administrator\Desktop\MAIN\Data.xlsx'!SERIAL,$G$48,'C:\Documents and Settings\Administrator\Desktop\MAIN\Data.xlsx'!SYSTEM,G$49)

Ok.. can you upload both the files.. would suggest you to put both the files in a folder and then zip that folder and upload. thanks


Regards,
DILIPandey

<click on below 'star' if this helps>

Looks like it is impossible right? To have names in another spreadsheet and reference it in a formula.

Yes.....!! You got it..

Scope of defined names are limited to that particular workbook only.. so suggest you to use the range reference instead of defined name in other workbook.. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>