INDRECT overkill?

[Phelony]

Hi Guys

I'm trying to build a spreadsheet with data drawn from many many many other workbooks. I've looked at several solutions and am trying to make use of the INDIRECT function, although I think I might be pushing it too hard (or there is the chance I'm just doing it wrong! )

The formula is:

=INDIRECT("'"&"["&C28 &"]"& $A28 & "'!" & D$1)

Where Column C contains the FULL filepath and name, Column A contains the sheet name and row 1 contains the cell references.

As far as I can see, this should work. I've gone over the layout many times but continually get #REF errors as a result.

Could anyone shed any light on what I'm doing wrong?

Just to clarify, this is to find a solution for pulling data from 224 cells across 500+ sheets divided across 58 workbooks which all need to be collated into a single sheet. (joy)

Using Excel 2003 because my company is cheap!

Thanks

Phel

 

[Phelony]

Solved!

Column C can't contain the full filename and filepath, only the filename.

It won't pull data through without the file being open, and when it is opened the formula doesn't work with the full filepath being in place!

Hitting myself over the head as you read.

 

[Phelony]

Can't figure out how to mark this as solved?

 

[RoryA]

We don't have a facility to mark questions solved, so nothing to worry about there.