Hi Guys
I have read a lot of threads on this, but it seems to be one thing which is a mammoth struggle in Excel.
The challenge:
I know that someone once wrote INDIRECT.EXT which may have helped, but it was available embedded within an addin which I am not sure will work (found it for versions up to Excel 97, I am on Office 365) plus I am not sure that I can go downloading unofficial addins.
I also tried the old EVALUATE (embedded within Name Manager) but that also required the source files to be open, which is just not practical.
Finally, I thought I had cracked it with this little code I found:
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function
Sadly - same result, source files must be open.
I would very much like to avoid building one line of the resource plan (for one file) then CTRL H the file name for every subsequent row! Firstly that will take ages and secondly, it will have to be repeated every new period.
Can anyone provide any advice on how I can get around the open source file issue with INDIRECT, to be able to drag my formulae down, picking up a new file ref on each row and returning the individual values accordingly - without all source data having to be open?
(This way, if someone changes their filename (you know it will happen) I can simply check and tweak the filename in Column B and all is well)
I have read a lot of threads on this, but it seems to be one thing which is a mammoth struggle in Excel.
The challenge:
- I need to produce a MASTER Resource Plan, which will consolidate individual team members planning into one overview.
- The source workbooks are all consistently structured, multi tab files.
- I only need to pull values - not calculate anything
- The data I wish to pull is not a flat file format
I know that someone once wrote INDIRECT.EXT which may have helped, but it was available embedded within an addin which I am not sure will work (found it for versions up to Excel 97, I am on Office 365) plus I am not sure that I can go downloading unofficial addins.
I also tried the old EVALUATE (embedded within Name Manager) but that also required the source files to be open, which is just not practical.
Finally, I thought I had cracked it with this little code I found:
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function
Sadly - same result, source files must be open.
I would very much like to avoid building one line of the resource plan (for one file) then CTRL H the file name for every subsequent row! Firstly that will take ages and secondly, it will have to be repeated every new period.
Can anyone provide any advice on how I can get around the open source file issue with INDIRECT, to be able to drag my formulae down, picking up a new file ref on each row and returning the individual values accordingly - without all source data having to be open?
(This way, if someone changes their filename (you know it will happen) I can simply check and tweak the filename in Column B and all is well)
