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

have a look in the help index for INDIRECT

--
Don Guillett
SalesAid Software
[email protected]
"Magnivy" <[email protected]> wrote in message
news:[email protected]...
>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
>

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

"Don Guillett" wrote:

> have a look in the help index for INDIRECT
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Magnivy" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
>
>
>

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
[email protected]
"Magnivy" <[email protected]> wrote in message
news:[email protected]...
>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
>

May I see a sample File?

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
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:%[email protected]...
> 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
> [email protected]
> "Magnivy" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

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
> [email protected]
> "Don Guillett" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> > [email protected]
> > "Magnivy" <[email protected]> wrote in message
> > news:[email protected]...
> >>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
> >>
> >
> >
>
>
>

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

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
[email protected]
"Magnivy" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> "Don Guillett" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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
>> > [email protected]
>> > "Magnivy" <[email protected]> wrote in message
>> > news:[email protected]...
>> >>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
>> >>
>> >
>> >
>>
>>
>>

Originally Posted by Magnivy

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,

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