VBA Code to Return Text Embeded in the VLOOKUP Function

G

Guest

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
 
G

Guest

I tried to use INDIRECT, but it has the same problem

Don Guillett said:
have a look in the help index for INDIRECT

--
Don Guillett
SalesAid Software
(e-mail address removed)
Magnivy said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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.
 
D

Don Guillett

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
(e-mail address removed)
Don Guillett said:
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
(e-mail address removed)
Magnivy said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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
(e-mail address removed)
Don Guillett said:
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
(e-mail address removed)
Magnivy said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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
 
D

Don Guillett

right click sheet tab>view code>insert 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
(e-mail address removed)
Magnivy said:
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 said:
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
(e-mail address removed)
Don Guillett said:
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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 
V

vane0326

Magnivy said:
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
Click to expand...


When you have a chance attach a small sample file to this thread
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help in VLOOKUP (second attempt) 8
vlookup not working with defined name 3
modifiying a custom lookup function 2
lookup help needed 2
How to insert VLOOKUP function in VBA? 6
Dynamic col_index_num in VLOOKUP? 5
VLOOKUP in VBA 1
Help with Custom Function 1

Top