avanced vlookup? off of other cells

[Sunvisor]

I have a sheet with the names of the file path...in cells A1-400. I need to use these files paths as the location of where to get the range...how would I incorporate that...when i select it, it just shows up as A2...then it will think that is the range instead of the director to the actual range in the directory whihc would be A1:G200.

Thanks for any ideas

 

[Derek Brown]

Have a look at the INDIRECT function.
But be aware that it can affect performance if it is used too much - it is a 'volatile' function (more information at http://www.decisionmodels.com/calcsecretsi.htm).

 

[prabby25101981]

Something like this IF -

 

[prabby25101981]

Something like this if A1 includes the filename -

=INDIRECT(A1&"!A2",1)

This will return the value included in A2 cell of sheet name given in cell A1. So, if you have "Sheet1" given in A1, the above formula will return Sheet1!A2 (value given in A2 of Sheet1.

The first part of the formula will change if there are spaces in the sheet name. e.g. A1 includes "Sheet 1" and you need to pickup value in A2 of this sheet, then the formula will be -

=INDIRECT("'",A1,"'!A2",1)