Locating a Worksheet from another Workbook

[Walter]

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

 

[Sheeloo]

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

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

 

[Walter]

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?

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

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

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

 

[Sheeloo]

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...

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?

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

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

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

 

[Walter]

Thanks please do, becasue for whatever reason I can not seem to get it 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...

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?

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

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

:

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

 

[Sheeloo]

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...

Thanks please do, becasue for whatever reason I can not seem to get it 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...

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?
:

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

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

:

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

 

[Walter]

I downloaded the file and I see the same error message #REF. Could it be
becasue I am useing Excel 2003, and you are using Excel 2007. I apologize
for not stating the version before, but I never gave it thought that it would
matter.

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...

Thanks please do, becasue for whatever reason I can not seem to get it 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...

:

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?
:

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

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

:

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

 

[Sheeloo]

Both files should be open for the formula to work... that is the biggest
limitation of INDIRECT...

It should work in 2003... You had indirectly told me when you changed xlsx
to xls

Try this;
Download both in the same directory...
Verify that the formula is still
=INDIRECT("'[Workbook A.xls]" & A5 & "'!B5")

I downloaded the file and I see the same error message #REF. Could it be
becasue I am useing Excel 2003, and you are using Excel 2007. I apologize
for not stating the version before, but I never gave it thought that it would
matter.

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...

Thanks please do, becasue for whatever reason I can not seem to get it 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...

:

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?
:

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

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

:

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

 

[Sheeloo]

Can you save the screenshot to a file, upload that file to wikisend.com and
paste the link here?

Or send it me by mail?

I am assuming you are working on the files I had sent... if not then first
make the formula work in my file...

What I see when I click on the evaluate box beside the error [#REF!] is that
the amswer shows correctly, but then it saids that 'the next evaluation will
result in an error', so what am I doing wrong.

Both files should be open for the formula to work... that is the biggest
limitation of INDIRECT...

It should work in 2003... You had indirectly told me when you changed xlsx
to xls

Try this;
Download both in the same directory...
Verify that the formula is still
=INDIRECT("'[Workbook A.xls]" & A5 & "'!B5")

I downloaded the file and I see the same error message #REF. Could it be
becasue I am useing Excel 2003, and you are using Excel 2007. I apologize
for not stating the version before, but I never gave it thought that it would
matter.

:

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...

:

Thanks please do, becasue for whatever reason I can not seem to get it 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...

:

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?
:

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

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

:

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

 

[Walter]

What I see when I click on the evaluate box beside the error [#REF!] is that
the amswer shows correctly, but then it saids that 'the next evaluation will
result in an error', so what am I doing wrong.

Both files should be open for the formula to work... that is the biggest
limitation of INDIRECT...

It should work in 2003... You had indirectly told me when you changed xlsx
to xls

Try this;
Download both in the same directory...
Verify that the formula is still
=INDIRECT("'[Workbook A.xls]" & A5 & "'!B5")

I downloaded the file and I see the same error message #REF. Could it be
becasue I am useing Excel 2003, and you are using Excel 2007. I apologize
for not stating the version before, but I never gave it thought that it would
matter.

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...

:

Thanks please do, becasue for whatever reason I can not seem to get it 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...

:

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?
:

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

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

:

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

 

[Walter]

I just sent you an E-Mail-with a screen shot showing the error message

Can you save the screenshot to a file, upload that file to wikisend.com and
paste the link here?

Or send it me by mail?

I am assuming you are working on the files I had sent... if not then first
make the formula work in my file...

What I see when I click on the evaluate box beside the error [#REF!] is that
the amswer shows correctly, but then it saids that 'the next evaluation will
result in an error', so what am I doing wrong.

Both files should be open for the formula to work... that is the biggest
limitation of INDIRECT...

It should work in 2003... You had indirectly told me when you changed xlsx
to xls

Try this;
Download both in the same directory...
Verify that the formula is still
=INDIRECT("'[Workbook A.xls]" & A5 & "'!B5")

:

I downloaded the file and I see the same error message #REF. Could it be
becasue I am useing Excel 2003, and you are using Excel 2007. I apologize
for not stating the version before, but I never gave it thought that it would
matter.

:

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...

:

Thanks please do, becasue for whatever reason I can not seem to get it 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...

:

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?
:

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

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

:

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

 

[Sheeloo]

Did not get it...

id is
(e-mail address removed)
just remove all As...

 

[Walter]

Ok just re-sent

Did not get it...

id is
(e-mail address removed)
just remove all As...