Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
I am trying to use the VLOOKUP Function to look up values from different
workbooks. Instead of manually typing in the "table_array", I want the "table_array" to be specified in a cell, and am trying to use an embedded Text Function to return the "table_arraray€¯ from that cell. For example, suppose cell A1 of Workbook 1 specifies the "table_array", which is contained in Workbook 2, as: 'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100 The formula that I have been using is: VLOOKUP(B5,TEXT(A1,""),3,FALSE) However, this returns an error because the TEXT(A1,"") formula returns the table_array as "'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100", instead of the above. This generates an error, because the formula returns parentheses at the beginning and end of the "table_array" (I can see that by clicking on "Show Calculation Steps"). I cant think of a way to remove those parentheses. Maybe there is a way to use VBA code to create a function that would return the contents of cell A1 without the parentheses. If you can think of a way to accomplish this, please let me know. Any assistance you provide would be GREATLY appreciated. Magnivy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
Either add an additional ' in front of your text or put " ' " in the
indirect formula =VLOOKUP(C1,INDIRECT("'"&C2&""),3) Be advised that indirect will NOT work on a closed workbook. -- Don Guillett SalesAid Software "Magnivy" wrote in message ... I am trying to use the VLOOKUP Function to look up values from different workbooks. Instead of manually typing in the "table_array", I want the "table_array" to be specified in a cell, and am trying to use an embedded Text Function to return the "table_arraray" from that cell. For example, suppose cell A1 of Workbook 1 specifies the "table_array", which is contained in Workbook 2, as: 'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100 The formula that I have been using is: VLOOKUP(B5,TEXT(A1,""),3,FALSE) However, this returns an error because the TEXT(A1,"") formula returns the table_array as "'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100", instead of the above. This generates an error, because the formula returns parentheses at the beginning and end of the "table_array" (I can see that by clicking on "Show Calculation Steps"). I can't think of a way to remove those parentheses. Maybe there is a way to use VBA code to create a function that would return the contents of cell A1 without the parentheses. If you can think of a way to accomplish this, please let me know. Any assistance you provide would be GREATLY appreciated. Magnivy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
May I see a sample File*?* -- vane0326 ------------------------------------------------------------------------ vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731 View this thread: http://www.excelforum.com/showthread...hreadid=526872 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
03/21/2006
'C:\yourfolder\[yourfile.xls]yoursheet'!$A$7:$H$62 =VLOOKUP(C1,INDIRECT("'"&C2&""),3) Again, Indirect only works on files that are OPEN. Else #Ref -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Either add an additional ' in front of your text or put " ' " in the indirect formula =VLOOKUP(C1,INDIRECT("'"&C2&""),3) Be advised that indirect will NOT work on a closed workbook. -- Don Guillett SalesAid Software "Magnivy" wrote in message ... I am trying to use the VLOOKUP Function to look up values from different workbooks. Instead of manually typing in the "table_array", I want the "table_array" to be specified in a cell, and am trying to use an embedded Text Function to return the "table_arraray" from that cell. For example, suppose cell A1 of Workbook 1 specifies the "table_array", which is contained in Workbook 2, as: 'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100 The formula that I have been using is: VLOOKUP(B5,TEXT(A1,""),3,FALSE) However, this returns an error because the TEXT(A1,"") formula returns the table_array as "'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100", instead of the above. This generates an error, because the formula returns parentheses at the beginning and end of the "table_array" (I can see that by clicking on "Show Calculation Steps"). I can't think of a way to remove those parentheses. Maybe there is a way to use VBA code to create a function that would return the contents of cell A1 without the parentheses. If you can think of a way to accomplish this, please let me know. Any assistance you provide would be GREATLY appreciated. Magnivy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
Don,
Thanks a lot for your help! I have been able to get your formula to work, but with a slightly different syntex: =VLOOKUP(C1,INDIRECT(C2&""),3). Would you happen to know of a way to make it work when the source file, 'C:\yourfolder\[yourfile.xls]yoursheet'!$A$7:$H$62 for instance, is closed? "Don Guillett" wrote: 03/21/2006 'C:\yourfolder\[yourfile.xls]yoursheet'!$A$7:$H$62 =VLOOKUP(C1,INDIRECT("'"&C2&""),3) Again, Indirect only works on files that are OPEN. Else #Ref -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Either add an additional ' in front of your text or put " ' " in the indirect formula =VLOOKUP(C1,INDIRECT("'"&C2&""),3) Be advised that indirect will NOT work on a closed workbook. -- Don Guillett SalesAid Software "Magnivy" wrote in message ... I am trying to use the VLOOKUP Function to look up values from different workbooks. Instead of manually typing in the "table_array", I want the "table_array" to be specified in a cell, and am trying to use an embedded Text Function to return the "table_arraray" from that cell. For example, suppose cell A1 of Workbook 1 specifies the "table_array", which is contained in Workbook 2, as: 'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100 The formula that I have been using is: VLOOKUP(B5,TEXT(A1,""),3,FALSE) However, this returns an error because the TEXT(A1,"") formula returns the table_array as "'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100", instead of the above. This generates an error, because the formula returns parentheses at the beginning and end of the "table_array" (I can see that by clicking on "Show Calculation Steps"). I can't think of a way to remove those parentheses. Maybe there is a way to use VBA code to create a function that would return the contents of cell A1 without the parentheses. If you can think of a way to accomplish this, please let me know. Any assistance you provide would be GREATLY appreciated. Magnivy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
Hey vane0326,
Is it possible to send you a sample file through the forum? I not, Is it possible to have your email address so I can send you an email file? Please let me know. Thanks, Magnivy "vane0326" wrote: May I see a sample File*?* -- vane0326 ------------------------------------------------------------------------ vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731 View this thread: http://www.excelforum.com/showthread...hreadid=526872 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
right click sheet tabview codeinsert this. Now when you change cell c1, e1
will get the formula. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$1" Then Exit Sub Range("e1").Formula = _ "=vlookup(c1,'" & Range("c2") & ",3)" End Sub -- Don Guillett SalesAid Software "Magnivy" wrote in message ... Don, Thanks a lot for your help! I have been able to get your formula to work, but with a slightly different syntex: =VLOOKUP(C1,INDIRECT(C2&""),3). Would you happen to know of a way to make it work when the source file, 'C:\yourfolder\[yourfile.xls]yoursheet'!$A$7:$H$62 for instance, is closed? "Don Guillett" wrote: 03/21/2006 'C:\yourfolder\[yourfile.xls]yoursheet'!$A$7:$H$62 =VLOOKUP(C1,INDIRECT("'"&C2&""),3) Again, Indirect only works on files that are OPEN. Else #Ref -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Either add an additional ' in front of your text or put " ' " in the indirect formula =VLOOKUP(C1,INDIRECT("'"&C2&""),3) Be advised that indirect will NOT work on a closed workbook. -- Don Guillett SalesAid Software "Magnivy" wrote in message ... I am trying to use the VLOOKUP Function to look up values from different workbooks. Instead of manually typing in the "table_array", I want the "table_array" to be specified in a cell, and am trying to use an embedded Text Function to return the "table_arraray" from that cell. For example, suppose cell A1 of Workbook 1 specifies the "table_array", which is contained in Workbook 2, as: 'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100 The formula that I have been using is: VLOOKUP(B5,TEXT(A1,""),3,FALSE) However, this returns an error because the TEXT(A1,"") formula returns the table_array as "'C:\Example\[Workbook 2.xls]Example'!$A$1:$G$100", instead of the above. This generates an error, because the formula returns parentheses at the beginning and end of the "table_array" (I can see that by clicking on "Show Calculation Steps"). I can't think of a way to remove those parentheses. Maybe there is a way to use VBA code to create a function that would return the contents of cell A1 without the parentheses. If you can think of a way to accomplish this, please let me know. Any assistance you provide would be GREATLY appreciated. Magnivy |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
Magnivy Wrote: Hey vane0326, Is it possible to send you a sample file through the forum? I not, I it possible to have your email address so I can send you an email file? Please let me know. Thanks, Magnivy "vane0326" wrote: May I see a sample File*?* -- vane0326 ------------------------------------------------------------------------ vane0326's Profile http://www.excelforum.com/member.php...o&userid=14731 View this thread http://www.excelforum.com/showthread...hreadid=526872 When you have a chance attach a small sample file to this thread -- vane032 ----------------------------------------------------------------------- vane0326's Profile: http://www.excelforum.com/member.php...fo&userid=1473 View this thread: http://www.excelforum.com/showthread.php?threadid=52687 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Return Text Embeded in the VLOOKUP Function
Vane0326,
With some effort (and headache, lol), I figured out a way to make it work using macros. Thanks a lot for responding to my question! Magnivy "vane0326" wrote: Magnivy Wrote: Hey vane0326, Is it possible to send you a sample file through the forum? I not, Is it possible to have your email address so I can send you an email file? Please let me know. Thanks, Magnivy "vane0326" wrote: May I see a sample File*?* -- vane0326 ------------------------------------------------------------------------ vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731 View this thread: http://www.excelforum.com/showthread...hreadid=526872 When you have a chance attach a small sample file to this thread. -- vane0326 ------------------------------------------------------------------------ vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731 View this thread: http://www.excelforum.com/showthread...hreadid=526872 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#N/A in VLookup - Can I use another function that will return 0? | Excel Worksheet Functions | |||
VLOOKUP return text not date | Excel Worksheet Functions | |||
How can I use the vlookup function to return a sum of the values? | Excel Discussion (Misc queries) | |||
vlookup function return all values | Excel Worksheet Functions | |||
Is AUTOSAVE FUNCTION embeded in the software or do I have to inst. | Excel Worksheet Functions |