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

Use
=INDIRECT("'[Workbook A.xlsx]" & A5 & "'!B5")

assuming sheetname in A5 ... it will return the value in B5 from the right
sheet

"Walter" wrote:

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

Thanks for the help, but right now all I get is an error message #REF

I took out the second "x" in the cell address beleiving that it was a typo,
other then that, messed around the the quotes some but still could not the
the equation to work

Any further Help?
"Sheeloo" wrote:

Use
=INDIRECT("'[Workbook A.xlsx]" & A5 & "'!B5")

assuming sheetname in A5 ... it will return the value in B5 from the right
sheet

"Walter" wrote:

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

It was not a typo but I should have warned you about it... I work in Excel
2007 which saves in XLSX format...

You were right to remove the x...

The most important thing I forgot to tell you ... with INDIRECT the other
workbook has to be open to get the data...

I can send you the sample workbooks if it still does not work for you...

"Walter" wrote:

Thanks for the help, but right now all I get is an error message #REF

I took out the second "x" in the cell address beleiving that it was a typo,
other then that, messed around the the quotes some but still could not the
the equation to work

Any further Help?
"Sheeloo" wrote:

Use
=INDIRECT("'[Workbook A.xlsx]" & A5 & "'!B5")

assuming sheetname in A5 ... it will return the value in B5 from the right
sheet

"Walter" wrote:

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

Thanks please do, becasue for whatever reason I can not seem to get it to work.

"Sheeloo" wrote:

It was not a typo but I should have warned you about it... I work in Excel
2007 which saves in XLSX format...

You were right to remove the x...

The most important thing I forgot to tell you ... with INDIRECT the other
workbook has to be open to get the data...

I can send you the sample workbooks if it still does not work for you...

"Walter" wrote:

Thanks for the help, but right now all I get is an error message #REF

I took out the second "x" in the cell address beleiving that it was a typo,
other then that, messed around the the quotes some but still could not the
the equation to work

Any further Help?
"Sheeloo" wrote:

Use
=INDIRECT("'[Workbook A.xlsx]" & A5 & "'!B5")

assuming sheetname in A5 ... it will return the value in B5 from the right
sheet

"Walter" wrote:

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

Download two files using the links below;

http://wikisend.com/download/928986/Sample List.xls

http://wikisend.com/download/886570/Workbook A.xls

Open them...and see

See the formula in Sample List...

"Walter" wrote:

Thanks please do, becasue for whatever reason I can not seem to get it to work.

"Sheeloo" wrote:

It was not a typo but I should have warned you about it... I work in Excel
2007 which saves in XLSX format...

You were right to remove the x...

The most important thing I forgot to tell you ... with INDIRECT the other
workbook has to be open to get the data...

I can send you the sample workbooks if it still does not work for you...

"Walter" wrote:

Thanks for the help, but right now all I get is an error message #REF

I took out the second "x" in the cell address beleiving that it was a typo,
other then that, messed around the the quotes some but still could not the
the equation to work

Any further Help?
"Sheeloo" wrote:

Use
=INDIRECT("'[Workbook A.xlsx]" & A5 & "'!B5")

assuming sheetname in A5 ... it will return the value in B5 from the right
sheet

"Walter" wrote:

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