Reference to external data

[VictorSueno]

I have oversimplified the following scenario in order to get to a specific question...

I have the following two excel files:
File #1 = ModelData.xls
File #2 = DistrictFormat.xls

File #1 contains one worksheet named "basn". The worksheet includes a column with header "NAME" and text data below it.

File #2 contains two worksheets; "Notes" and "BASIN_GN"

The "Notes" worksheet contains a cell (A1) with the following value

'C:\ProjectName\[ModelData.xls]basn'!$A:$V

The "BASINS_GN" contains a column (column A) with the header "NAME" but no data below it.

Now here's my question...

I would like to use a HLOOKUP function in worksheet BASIN_GN to return a value from the column "NAME" in the basn worksheet. The hitch is that I don't want to use the value in cell A1 of the Notes worksheet instead of hard entering the lookup range. The following is the formula that I used.

=HLOOKUP("NAME",Notes!A1,1,0)

Why doesn't it work. How can I make this work so that I can change the value in cell A1 so that HLOOKUP function will retreive data from different files (when value in A1 is changed)?

I realize that it is setting Notes!A1 as the lookup range. How can I get it to do what I want instead? Thank you for your help.

 

[VictorSueno]

Nevermind.I just figured it out. Use INDIRECT().