Thread
:
VBA Code to Return Text Embeded in the VLOOKUP Function
View Single Post
#
6
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett