If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
=indirect.ext() will need to know what folder to look in and you didn't include
that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#12
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
=INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1")
This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#13
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
Create the formula that works when you just retrieve the value from the closed
workbook. I should look something like this: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 Then you'll have to share what's in Sheet1!a4. Is it a date? Is it text? Or what? If it's really a date, did you try adding the =text() function to your =indirect.ext() expression? Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1") This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#14
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
This is what I tried:
=INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&TEXT(Sheet1!A4,"dd-mmm-yy")&"'!A1") I tried both number and text formatting in related cells, and it works when "test" is open but not when closed? I can send this to you if it would be easier? I don't see any reason for it not to work myself? -- Thank you! "Dave Peterson" wrote: Create the formula that works when you just retrieve the value from the closed workbook. I should look something like this: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 Then you'll have to share what's in Sheet1!a4. Is it a date? Is it text? Or what? If it's really a date, did you try adding the =text() function to your =indirect.ext() expression? Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1") This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#15
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
No thanks to the file.
Does: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 return the correct value? (assuming that May 10, 2010 is in Sheet1 A4. what does: =text(sheet1!a4,"dd-mmm-yy") return? What does: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1") return? Doug wrote: This is what I tried: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&TEXT(Sheet1!A4,"dd-mmm-yy")&"'!A1") I tried both number and text formatting in related cells, and it works when "test" is open but not when closed? I can send this to you if it would be easier? I don't see any reason for it not to work myself? -- Thank you! "Dave Peterson" wrote: Create the formula that works when you just retrieve the value from the closed workbook. I should look something like this: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 Then you'll have to share what's in Sheet1!a4. Is it a date? Is it text? Or what? If it's really a date, did you try adding the =text() function to your =indirect.ext() expression? Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1") This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#16
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
25 (correct value)
10-May-10 #REF! Respectively "Dave Peterson" wrote: No thanks to the file. Does: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 return the correct value? (assuming that May 10, 2010 is in Sheet1 A4. what does: =text(sheet1!a4,"dd-mmm-yy") return? What does: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1") return? Doug wrote: This is what I tried: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&TEXT(Sheet1!A4,"dd-mmm-yy")&"'!A1") I tried both number and text formatting in related cells, and it works when "test" is open but not when closed? I can send this to you if it would be easier? I don't see any reason for it not to work myself? -- Thank you! "Dave Peterson" wrote: Create the formula that works when you just retrieve the value from the closed workbook. I should look something like this: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 Then you'll have to share what's in Sheet1!a4. Is it a date? Is it text? Or what? If it's really a date, did you try adding the =text() function to your =indirect.ext() expression? Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1") This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#17
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
Still no fix, but here is the results of what you told me to do:
25 (correct value) 10-May-10 #REF! Respectively -- Thank you! "Dave Peterson" wrote: No thanks to the file. Does: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 return the correct value? (assuming that May 10, 2010 is in Sheet1 A4. what does: =text(sheet1!a4,"dd-mmm-yy") return? What does: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1") return? Doug wrote: This is what I tried: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&TEXT(Sheet1!A4,"dd-mmm-yy")&"'!A1") I tried both number and text formatting in related cells, and it works when "test" is open but not when closed? I can send this to you if it would be easier? I don't see any reason for it not to work myself? -- Thank you! "Dave Peterson" wrote: Create the formula that works when you just retrieve the value from the closed workbook. I should look something like this: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 Then you'll have to share what's in Sheet1!a4. Is it a date? Is it text? Or what? If it's really a date, did you try adding the =text() function to your =indirect.ext() expression? Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1") This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#18
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
If this:
=text(sheet1!a4,"dd-mmm-yy") returns a reference error, then there's a good chance you don't have a sheet named Sheet1. Maybe this has been a typo all along????? Doug wrote: 25 (correct value) 10-May-10 #REF! Respectively "Dave Peterson" wrote: No thanks to the file. Does: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 return the correct value? (assuming that May 10, 2010 is in Sheet1 A4. what does: =text(sheet1!a4,"dd-mmm-yy") return? What does: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1") return? Doug wrote: This is what I tried: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&TEXT(Sheet1!A4,"dd-mmm-yy")&"'!A1") I tried both number and text formatting in related cells, and it works when "test" is open but not when closed? I can send this to you if it would be easier? I don't see any reason for it not to work myself? -- Thank you! "Dave Peterson" wrote: Create the formula that works when you just retrieve the value from the closed workbook. I should look something like this: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 Then you'll have to share what's in Sheet1!a4. Is it a date? Is it text? Or what? If it's really a date, did you try adding the =text() function to your =indirect.ext() expression? Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1") This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#19
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
It returned 10-May-10
This is crazy... "Dave Peterson" wrote: If this: =text(sheet1!a4,"dd-mmm-yy") returns a reference error, then there's a good chance you don't have a sheet named Sheet1. Maybe this has been a typo all along????? Doug wrote: 25 (correct value) 10-May-10 #REF! Respectively "Dave Peterson" wrote: No thanks to the file. Does: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 return the correct value? (assuming that May 10, 2010 is in Sheet1 A4. what does: =text(sheet1!a4,"dd-mmm-yy") return? What does: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1") return? Doug wrote: This is what I tried: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&TEXT(Sheet1!A4,"dd-mmm-yy")&"'!A1") I tried both number and text formatting in related cells, and it works when "test" is open but not when closed? I can send this to you if it would be easier? I don't see any reason for it not to work myself? -- Thank you! "Dave Peterson" wrote: Create the formula that works when you just retrieve the value from the closed workbook. I should look something like this: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 Then you'll have to share what's in Sheet1!a4. Is it a date? Is it text? Or what? If it's really a date, did you try adding the =text() function to your =indirect.ext() expression? Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1") This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#20
|
|||
|
|||
Referencing another workbook using a variable for the sheet?
I'm out of guesses.
Sorry. Doug wrote: It returned 10-May-10 This is crazy... "Dave Peterson" wrote: If this: =text(sheet1!a4,"dd-mmm-yy") returns a reference error, then there's a good chance you don't have a sheet named Sheet1. Maybe this has been a typo all along????? Doug wrote: 25 (correct value) 10-May-10 #REF! Respectively "Dave Peterson" wrote: No thanks to the file. Does: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 return the correct value? (assuming that May 10, 2010 is in Sheet1 A4. what does: =text(sheet1!a4,"dd-mmm-yy") return? What does: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1") return? Doug wrote: This is what I tried: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&TEXT(Sheet1!A4,"dd-mmm-yy")&"'!A1") I tried both number and text formatting in related cells, and it works when "test" is open but not when closed? I can send this to you if it would be easier? I don't see any reason for it not to work myself? -- Thank you! "Dave Peterson" wrote: Create the formula that works when you just retrieve the value from the closed workbook. I should look something like this: ='C:\Users\The King''s\Desktop\[test.xlsx]10-may-10'!A1 Then you'll have to share what's in Sheet1!a4. Is it a date? Is it text? Or what? If it's really a date, did you try adding the =text() function to your =indirect.ext() expression? Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&Sheet1!A4&"'!A1") This is the path and once I close the sending file, click in the cell of this formula and enter it goes back to #REF! Doesn't make sense? -- "Dave Peterson" wrote: =indirect.ext() will need to know what folder to look in and you didn't include that in your expression. I'd try this first: Open the sending workbook (test.xlsx). Then select the cell to retrieve Ctrl-c to copy it Paste into a cell in the receiving workbook (a test cell is fine). You should end up with something like: ='[book1.xls]Sheet1'!$A$1 Now close that sending workbook and excel will add the path to that formula: ='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1 This is the string you're trying to build to put inside the =indirect.ext() function. =indirect.ext("'C:\My Documents\excel\[test.xlsx]10-May-10'!$A$1") When you need to change the 10-may-10 portion so that it's retrieved from a different cell, you'll want to make sure that you retrieve it nicely. So it that value is really a date: =indirect.ext("'C:\My Documents\excel\[test.xlsx]" & text(a1,"dd-mmm-yy") & "'!$A$1") I'm not sure if it's in dmy order and I'm not sure if it should be a 4 character month (not abbreviated: dd-mmmm-yy Doug wrote: Ok, I will give you all the details. Thank you for your time and sorry for the length of this. Let me start over and explain everything... Here is the formula that I have been using referencing a sheet in my workbook. These cover an array of R3J504 just to give you an idea how many lookups I am currently using. These are mostly for # values. =IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE)) Now that I am wanting to reference a fetch file instead of a sheet in my workbook I would like to change this. In cell M1 of my workbook I have a drop down menu of dates such as "10-May-10" in text format. I am wanting to figure out the most efficient way to use this as a reference variable to sheet names in my fetch file. I am hopeful to do this with the fetch file closed so that I will never need to open it unless I find something doesn't make sense with the data I am seeing. I downloaded and installed the add-in for indirect.ext and it was still giving #Ref! when the book is closed. As a test apart from the formula above, I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") & =INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext". They both work with the workbook open, but not closed. I was reading an article about Harlan's Pull function and am wondering if this would be a good alternative. It aparently works whether the fetch file is open or closed and suppose to work with vlookups, but again I can't seem to figure out how the formula should be structured. Do you have any more questions to adaquately address my problem? What ever you think is best? -- Thank you! "Dave Peterson" wrote: Time to share the formula you used and all the values in each of the cells that that formula uses. Doug wrote: I just found that one of my problems is that it is still not reading it when the workbook is closed, but as soon as I open the workbook it changes from#REF! to the cell value. I have the add-in installed and the workbook recognizes the function indirect.ext so I am not sure what the problem could be? Any more suggestions? -- Thank you! "Dave Peterson" wrote: What's in A3 of sheet1? If it's text (like '0513), then use: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & 'sheet1'!A3 &"'!A1") if it's a number: =indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & text('Sheet1'!A3,"0000") &"'!A1") Watch those " marks. Don't surround everything with them! Doug wrote: =INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet 1'!A3)"&"'!A1") I am sorry! It is giving me a #ref! response with the above and if I put A1",,,,) at the end it gives me #value! -- Thank you! "Doug" wrote: =[test.xls]sheet1!A1 Is it possible to make the "sheet1" portion of this formula tied to a cell in the destination workbook so that if I type "sheet2" in cell A3 of my destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1 What I am trying to do is make all the references in my workbook dependent on what sheet# I have typed into cell A3. Up till now I have been fetching all the data based on a 4 digit date format such as "0513". "0513" would be the name of a sheet that was saved with data for may 13th. My hope is to be able to type 0513 into cell A3 and have it reference the data. Can this be done or is there a better way? Also I noticed that in order to update the formula cell I have to select the test workbook in the dialog box each time. Is there a way to make this a constant reference instead of having to ok it? Hopefully without having to open the reference workbook each time either? I am rather new to this... -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|