I have a problem that I cannot seem to find a solution for.

I have two workbooks. Workbook A is my data workbook, it has say six
worksheets. Named say for states. Oregon, Washington, California, Nevada,
Idaho and Arizona.
In each worksheet the data is in the same location in all. What that data
is right now is not important, to my problem.

Workbook B is my analysis workbook. In Sheet 1 of Workbook B in Column A
Row 5, I have a drop down list of all of the states that are listed in
Workbook A.

What I am after is when I select a State from the drop down list in cell A5,
that any cells referring to Workbook A use this selection in the link.

Example, In cell A5 of Workbook B I make a selection of say Oregon. So in
cell B5, I need it to use the selection in cell A5 in whatever link I have
established to that particular worksheet. Same for anything selection that I
might make in cell A5.

Cell A5 = Oregon or I select Idaho

C:\[Workbook A.xls] €œOregon€� €˜!B5 the double quotes applied for this example

or

C:\[Workbook A.xls] €�Idaho€� !B5 the double quotes applied for this example

I have tried using a cell reference to cell A5, but Excel does not like
that, so I am at a lost for how to get this to work