Excel 2007 : Use cell text as a filename reference for external data

I have it working as expected right now, but it's really sloppy and long. There has got to be a way to clean this up. I've thought about the INDIRECT formula, but the files can rarely be open at the same time. I've tried converting the cell to a string using Right(A1,LEN(A1), but I don't know how to put that into the filename reference. Anyway, here is what I have going for me:

Please Login or Register  to view this content.

That is a sample. There are a total of 6 or seven files, so this formula is too long and confusing to read. There has got to be a way to clean this up by doing something like '["&A3&".xls] or similar - instead of all those nested if statements...

Thanks!

That's how romperstomer solved it for me:
http://www.excelforum.com/showthread...=1#post2824330

Originally Posted by dip11

That's how romperstomer solved it for me:
http://www.excelforum.com/showthread...=1#post2824330

I think that's backwards from what I'm doing. I'm reading values from 6 or seven different files that the users will add data to. I'm just referncing that data in a spreadsheet that combines said data from those 6-7 files; the filenames of those user's files just so happen to be present in collumn A

Maybe I read your thread and romperstomper's code wrong though...