|
linking workbooks
Is there a formula that I can put in a cell that tells excel to match part of
the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. |
linking workbooks
you can try indirect
this looks at cell b8 and then goes to that sheet name, cell i7 and returns the value =INDIRECT(B8 & "!$I$17") -- Gary "Kaby" wrote in message ... Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. |
linking workbooks
Hi Gary....good idea...I tried that but I didn't get the result I wanted. I
don't know much about the Indirect function, but it may not work on closed workbooks--I think I read that somewhere...In any case, what I will like to do is actually provide the file name (or at least part of it), and for excel to go to that file and open and retrieve a cell value and close the file. "Gary Keramidas" wrote: you can try indirect this looks at cell b8 and then goes to that sheet name, cell i7 and returns the value =INDIRECT(B8 & "!$I$17") -- Gary "Kaby" wrote in message ... Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. |
linking workbooks
="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1"
This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. |
linking workbooks
You'd want to use the =indirect() function, but that only works when the
"sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson |
linking workbooks
Thanks Dave...I've now added the Indirect.EXT function and working with this
function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson |
linking workbooks
I don't use that addin, but maybe...
=INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson |
linking workbooks
sorry, missed the fact the workbook was closed
-- Gary "Kaby" wrote in message ... Hi Gary....good idea...I tried that but I didn't get the result I wanted. I don't know much about the Indirect function, but it may not work on closed workbooks--I think I read that somewhere...In any case, what I will like to do is actually provide the file name (or at least part of it), and for excel to go to that file and open and retrieve a cell value and close the file. "Gary Keramidas" wrote: you can try indirect this looks at cell b8 and then goes to that sheet name, cell i7 and returns the value =INDIRECT(B8 & "!$I$17") -- Gary "Kaby" wrote in message ... Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. |
linking workbooks
The first formula looks perfect...but when I tried it I get a #VALUE error...
"Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson |
linking workbooks
What happens if you actually type in the value in D86 into a cell with a similar
formula: Say D86 contains Book1 if you put =INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34") heck, if you just do it directly: ='C:\[book1.xls]BUYERSREQUEST'!E34 do either work? But since I haven't used this, I'm pretty much just guessing. Kaby wrote: The first formula looks perfect...but when I tried it I get a #VALUE error... "Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
linking workbooks
This is helpful...I feel like I am getting closer to programming that I want
to accomplish. When I type in this formula: ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" The cell value turns into this: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 and not the actual value found in cell c44. I am not sure why...but I think my syntax to my formula is wrong. Heck--doing it directly maybe better than indirectly... Kaby "Dave Peterson" wrote: What happens if you actually type in the value in D86 into a cell with a similar formula: Say D86 contains Book1 if you put =INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34") heck, if you just do it directly: ='C:\[book1.xls]BUYERSREQUEST'!E34 do either work? But since I haven't used this, I'm pretty much just guessing. Kaby wrote: The first formula looks perfect...but when I tried it I get a #VALUE error... "Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
linking workbooks
This formula just creates a nice string.
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" You could convert it to values (edit|copy, edit|paste special|values) and then you'd have this string: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Add an equal sign and it'll become a formula ='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Maybe you got the #value! error from the original workbook because the workbook didn't exist on the C: drive??? Kaby wrote: This is helpful...I feel like I am getting closer to programming that I want to accomplish. When I type in this formula: ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" The cell value turns into this: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 and not the actual value found in cell c44. I am not sure why...but I think my syntax to my formula is wrong. Heck--doing it directly maybe better than indirectly... Kaby "Dave Peterson" wrote: What happens if you actually type in the value in D86 into a cell with a similar formula: Say D86 contains Book1 if you put =INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34") heck, if you just do it directly: ='C:\[book1.xls]BUYERSREQUEST'!E34 do either work? But since I haven't used this, I'm pretty much just guessing. Kaby wrote: The first formula looks perfect...but when I tried it I get a #VALUE error... "Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
linking workbooks
Terrific...this is good...I will work with this more, but I feel very close
to a solution. Thanks a bunch....I will let you know if I have any more questions. Kaby "Dave Peterson" wrote: This formula just creates a nice string. ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" You could convert it to values (edit|copy, edit|paste special|values) and then you'd have this string: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Add an equal sign and it'll become a formula ='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Maybe you got the #value! error from the original workbook because the workbook didn't exist on the C: drive??? Kaby wrote: This is helpful...I feel like I am getting closer to programming that I want to accomplish. When I type in this formula: ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" The cell value turns into this: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 and not the actual value found in cell c44. I am not sure why...but I think my syntax to my formula is wrong. Heck--doing it directly maybe better than indirectly... Kaby "Dave Peterson" wrote: What happens if you actually type in the value in D86 into a cell with a similar formula: Say D86 contains Book1 if you put =INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34") heck, if you just do it directly: ='C:\[book1.xls]BUYERSREQUEST'!E34 do either work? But since I haven't used this, I'm pretty much just guessing. Kaby wrote: The first formula looks perfect...but when I tried it I get a #VALUE error... "Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
linking workbooks
I actually want to keep it as a string that turns into a formula so that when
I change the product name column, so does the filename and the linking. Is this possible--if it makes sense? "Kaby" wrote: Terrific...this is good...I will work with this more, but I feel very close to a solution. Thanks a bunch....I will let you know if I have any more questions. Kaby "Dave Peterson" wrote: This formula just creates a nice string. ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" You could convert it to values (edit|copy, edit|paste special|values) and then you'd have this string: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Add an equal sign and it'll become a formula ='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Maybe you got the #value! error from the original workbook because the workbook didn't exist on the C: drive??? Kaby wrote: This is helpful...I feel like I am getting closer to programming that I want to accomplish. When I type in this formula: ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" The cell value turns into this: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 and not the actual value found in cell c44. I am not sure why...but I think my syntax to my formula is wrong. Heck--doing it directly maybe better than indirectly... Kaby "Dave Peterson" wrote: What happens if you actually type in the value in D86 into a cell with a similar formula: Say D86 contains Book1 if you put =INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34") heck, if you just do it directly: ='C:\[book1.xls]BUYERSREQUEST'!E34 do either work? But since I haven't used this, I'm pretty much just guessing. Kaby wrote: The first formula looks perfect...but when I tried it I get a #VALUE error... "Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
linking workbooks
You either have to do that string to formula yourself -- or use something else.
That something else is =indirect.ext(). Maybe someone who uses =indirect.ext() can jump in with better instructions. Kaby wrote: I actually want to keep it as a string that turns into a formula so that when I change the product name column, so does the filename and the linking. Is this possible--if it makes sense? "Kaby" wrote: Terrific...this is good...I will work with this more, but I feel very close to a solution. Thanks a bunch....I will let you know if I have any more questions. Kaby "Dave Peterson" wrote: This formula just creates a nice string. ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" You could convert it to values (edit|copy, edit|paste special|values) and then you'd have this string: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Add an equal sign and it'll become a formula ='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Maybe you got the #value! error from the original workbook because the workbook didn't exist on the C: drive??? Kaby wrote: This is helpful...I feel like I am getting closer to programming that I want to accomplish. When I type in this formula: ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" The cell value turns into this: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 and not the actual value found in cell c44. I am not sure why...but I think my syntax to my formula is wrong. Heck--doing it directly maybe better than indirectly... Kaby "Dave Peterson" wrote: What happens if you actually type in the value in D86 into a cell with a similar formula: Say D86 contains Book1 if you put =INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34") heck, if you just do it directly: ='C:\[book1.xls]BUYERSREQUEST'!E34 do either work? But since I haven't used this, I'm pretty much just guessing. Kaby wrote: The first formula looks perfect...but when I tried it I get a #VALUE error... "Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
linking workbooks
This formula:
=INDIRECT.EXT('H:\["&D100&".xls]BUYERSREQUEST'!C44) keeps giving me a #value error...any obvious reasons.... "Dave Peterson" wrote: You either have to do that string to formula yourself -- or use something else. That something else is =indirect.ext(). Maybe someone who uses =indirect.ext() can jump in with better instructions. Kaby wrote: I actually want to keep it as a string that turns into a formula so that when I change the product name column, so does the filename and the linking. Is this possible--if it makes sense? "Kaby" wrote: Terrific...this is good...I will work with this more, but I feel very close to a solution. Thanks a bunch....I will let you know if I have any more questions. Kaby "Dave Peterson" wrote: This formula just creates a nice string. ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" You could convert it to values (edit|copy, edit|paste special|values) and then you'd have this string: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Add an equal sign and it'll become a formula ='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Maybe you got the #value! error from the original workbook because the workbook didn't exist on the C: drive??? Kaby wrote: This is helpful...I feel like I am getting closer to programming that I want to accomplish. When I type in this formula: ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" The cell value turns into this: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 and not the actual value found in cell c44. I am not sure why...but I think my syntax to my formula is wrong. Heck--doing it directly maybe better than indirectly... Kaby "Dave Peterson" wrote: What happens if you actually type in the value in D86 into a cell with a similar formula: Say D86 contains Book1 if you put =INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34") heck, if you just do it directly: ='C:\[book1.xls]BUYERSREQUEST'!E34 do either work? But since I haven't used this, I'm pretty much just guessing. Kaby wrote: The first formula looks perfect...but when I tried it I get a #VALUE error... "Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
linking workbooks
None from me.
Kaby wrote: This formula: =INDIRECT.EXT('H:\["&D100&".xls]BUYERSREQUEST'!C44) keeps giving me a #value error...any obvious reasons.... "Dave Peterson" wrote: You either have to do that string to formula yourself -- or use something else. That something else is =indirect.ext(). Maybe someone who uses =indirect.ext() can jump in with better instructions. Kaby wrote: I actually want to keep it as a string that turns into a formula so that when I change the product name column, so does the filename and the linking. Is this possible--if it makes sense? "Kaby" wrote: Terrific...this is good...I will work with this more, but I feel very close to a solution. Thanks a bunch....I will let you know if I have any more questions. Kaby "Dave Peterson" wrote: This formula just creates a nice string. ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" You could convert it to values (edit|copy, edit|paste special|values) and then you'd have this string: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Add an equal sign and it'll become a formula ='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 Maybe you got the #value! error from the original workbook because the workbook didn't exist on the C: drive??? Kaby wrote: This is helpful...I feel like I am getting closer to programming that I want to accomplish. When I type in this formula: ="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44" The cell value turns into this: 'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44 and not the actual value found in cell c44. I am not sure why...but I think my syntax to my formula is wrong. Heck--doing it directly maybe better than indirectly... Kaby "Dave Peterson" wrote: What happens if you actually type in the value in D86 into a cell with a similar formula: Say D86 contains Book1 if you put =INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34") heck, if you just do it directly: ='C:\[book1.xls]BUYERSREQUEST'!E34 do either work? But since I haven't used this, I'm pretty much just guessing. Kaby wrote: The first formula looks perfect...but when I tried it I get a #VALUE error... "Dave Peterson" wrote: I don't use that addin, but maybe... =INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34") And it looks like you tried to combine that function with =vlookup(). I still don't use it, but... =vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false) or something like that... Kaby wrote: Thanks Dave...I've now added the Indirect.EXT function and working with this function seems to be the way I should be headed. I did want to ask a question about that function...this may be a little difficult to explain, but I will give it a try anyway. I have a master workbook that I would like to link information from various workbooks placed in a folder. In the master workbook, there will be a column that will have the file name (at least part of it - to be exact the product name), and what I would like to do with this indirect faunction is match up that product name with the file name and retrieve a cell value from the matched workbook. I've tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't work....Are there any suggestions? "Dave Peterson" wrote: You'd want to use the =indirect() function, but that only works when the "sending" workbook is open. But Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Kaby wrote: ="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" This may the direction I want to go--but this one has date as a variant "Kaby" wrote: Is there a formula that I can put in a cell that tells excel to match part of the file name that I will provide for it in an adjacent cell, go into the file and link the data to the destination file. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 04:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com