Having issues with INDIRECT and cell references

Hi everyone,

I'm hoping this will be a fairly easy fix, I'm sure I've done something like this before but I don't use the INDIRECT function that often so hopefully I'm just missing something really simple....

I have over 100 workbooks all with the exactly same layout, it is a different set every month so the filenames are never the same (lots of people contribute and sometimes new ones get added/old ones get deleted etc.). I'm trying to make a summary workbook which draws the key data from all the workbooks.

To do this I have this formula... (with the workbook name written, [filename.xls], in P5)

=INDIRECT("'G:\Outputs\Results May 2013\"&$P5&"Overview_results'!$F$36")

however this always returns a #REF error rather than the value in F36. I'm not sure what I'm missing, I've got all the workbooks open so it can't be that.

Any help would be much appreciated, I can also post a skeleton version of the workbook if that would help.

Thanks,

Alice

What is in P5 ?

EDIT-
sorry missed it the first read

Not sure, but maybe this:
=INDIRECT("'[G:\Outputs\Results May 2013\"&$P5&"]'Overview_results'!$F$36")
would need to seen a sample workbook to go any further than that

Indirect won't work with closed workbooks

Use Indirect.Ext function of Morefunc Addin to get data from closed workbooks

Maybe this addin can help

Originally Posted by qaliq

I know! I have a macro which I run beforehand which opens all the workbooks in the folder.

If the files are already kept open then there is no need of root path

Glad you fixed it and thanks for the feedback