Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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. "Sheeloo" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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") "Walter" wrote: 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. "Sheeloo" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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... "Walter" wrote: 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. "Sheeloo" wrote: 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") "Walter" wrote: 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. "Sheeloo" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
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. "Sheeloo" wrote: 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") "Walter" wrote: 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. "Sheeloo" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
I just sent you an E-Mail-with a screen shot showing the error message
"Sheeloo" wrote: 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... "Walter" wrote: 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. "Sheeloo" wrote: 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") "Walter" wrote: 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. "Sheeloo" wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating a Worksheet from another Workbook
Ok just re-sent
"Sheeloo" wrote: Did not get it... id is just remove all As... "Walter" wrote: I just sent you an E-Mail-with a screen shot showing the error message |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating Comments in a Worksheet | Excel Discussion (Misc queries) | |||
Copy Data from Workbook into specific Worksheet in other Workbook? | Excel Discussion (Misc queries) | |||
Locating | New Users to Excel | |||
LOCATING A CHART SAVED IN A WORKBOOK | Excel Discussion (Misc queries) | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions |