formula construction w/ vba

Hi all,

i'm trying to construct a formula that will look like this in excel
=vlookup(O4,Month_A&"_A",2, false)

Month_A (R2) contains a drop-down data validation with a list of months
the user selects the month and then the formula takes that
appends _A to it
and that will be the name of the range it will lookup in

doing it the way i am, produces a string that apparently excel doesn't like,
or know what to do with.

is there a way to convert the string back to a reference that excel will
understand?

tia

J

=If(len(r2),vlookup(O4,Indirect(R2&"_A"),2, false),"")

--
Regards,
Tom Ogilvy


"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:925039FE-4F03-4D7D-A5A3-D26BA6573F68@microsoft.com...
> Hi all,
>
> i'm trying to construct a formula that will look like this in excel
> =vlookup(O4,Month_A&"_A",2, false)
>
> Month_A (R2) contains a drop-down data validation with a list of months
> the user selects the month and then the formula takes that
> appends _A to it
> and that will be the name of the range it will lookup in
>
> doing it the way i am, produces a string that apparently excel doesn't
like,
> or know what to do with.
>
> is there a way to convert the string back to a reference that excel will
> understand?
>
> tia
>
> J

Hi Tom,

does it matter that
Indirect(R2&"_A") refers to a range larger than just one cell?
i'm getting a #REF error on
Indirect(R2&"_A")

J

"Tom Ogilvy" wrote:

> =If(len(r2),vlookup(O4,Indirect(R2&"_A"),2, false),"")
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> news:925039FE-4F03-4D7D-A5A3-D26BA6573F68@microsoft.com...
> > Hi all,
> >
> > i'm trying to construct a formula that will look like this in excel
> > =vlookup(O4,Month_A&"_A",2, false)
> >
> > Month_A (R2) contains a drop-down data validation with a list of months
> > the user selects the month and then the formula takes that
> > appends _A to it
> > and that will be the name of the range it will lookup in
> >
> > doing it the way i am, produces a string that apparently excel doesn't
> like,
> > or know what to do with.
> >
> > is there a way to convert the string back to a reference that excel will
> > understand?
> >
> > tia
> >
> > J
>
>
>

Assume R2 contains the textstring

July

and you have a table of values named July_A2, then

Indirect(R2 & "_A")

would return a reference to that table of values which Vlookup is looking
for as the second argument.

That is what I understood you to say the situation is. Using the situation
I described, it works fine for me.

--
Regards,
Tom Ogilvy

"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:BCA131CF-2DF3-4652-B1B9-F54F54F52C5C@microsoft.com...
> Hi Tom,
>
> does it matter that
> Indirect(R2&"_A") refers to a range larger than just one cell?
> i'm getting a #REF error on
> Indirect(R2&"_A")
>
> J
>
> "Tom Ogilvy" wrote:
>
> > =If(len(r2),vlookup(O4,Indirect(R2&"_A"),2, false),"")
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > news:925039FE-4F03-4D7D-A5A3-D26BA6573F68@microsoft.com...
> > > Hi all,
> > >
> > > i'm trying to construct a formula that will look like this in excel
> > > =vlookup(O4,Month_A&"_A",2, false)
> > >
> > > Month_A (R2) contains a drop-down data validation with a list of
months
> > > the user selects the month and then the formula takes that
> > > appends _A to it
> > > and that will be the name of the range it will lookup in
> > >
> > > doing it the way i am, produces a string that apparently excel doesn't
> > like,
> > > or know what to do with.
> > >
> > > is there a way to convert the string back to a reference that excel
will
> > > understand?
> > >
> > > tia
> > >
> > > J
> >
> >
> >

Yup - thats exactly what i meant and what i wanted to do.

Having trouble figuring out why the #REF

i double checked the validity of the ranges using F5 and also in Insert ->
Name -> Define: they're all good.

there's got to be something else i'm not seeing

thanks again Tom, you're awesome!

J

"Tom Ogilvy" wrote:

> Assume R2 contains the textstring
>
> July
>
> and you have a table of values named July_A2, then
>
> Indirect(R2 & "_A")
>
> would return a reference to that table of values which Vlookup is looking
> for as the second argument.
>
> That is what I understood you to say the situation is. Using the situation
> I described, it works fine for me.
>
> --
> Regards,
> Tom Ogilvy
>
> "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> news:BCA131CF-2DF3-4652-B1B9-F54F54F52C5C@microsoft.com...
> > Hi Tom,
> >
> > does it matter that
> > Indirect(R2&"_A") refers to a range larger than just one cell?
> > i'm getting a #REF error on
> > Indirect(R2&"_A")
> >
> > J
> >
> > "Tom Ogilvy" wrote:
> >
> > > =If(len(r2),vlookup(O4,Indirect(R2&"_A"),2, false),"")
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > > news:925039FE-4F03-4D7D-A5A3-D26BA6573F68@microsoft.com...
> > > > Hi all,
> > > >
> > > > i'm trying to construct a formula that will look like this in excel
> > > > =vlookup(O4,Month_A&"_A",2, false)
> > > >
> > > > Month_A (R2) contains a drop-down data validation with a list of
> months
> > > > the user selects the month and then the formula takes that
> > > > appends _A to it
> > > > and that will be the name of the range it will lookup in
> > > >
> > > > doing it the way i am, produces a string that apparently excel doesn't
> > > like,
> > > > or know what to do with.
> > > >
> > > > is there a way to convert the string back to a reference that excel
> will
> > > > understand?
> > > >
> > > > tia
> > > >
> > > > J
> > >
> > >
> > >
>
>
>

All I can say is that the approach is sound. I suspect if you look closely,
there is some error in your implementation. Try getting it working in a
new workbook in a simplified environment and perhaps you will see what you
need to do.

--
Regards,
Tom Ogilvy

"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:E065623F-1D6E-4D13-8DDF-F55E83E15206@microsoft.com...
> Yup - thats exactly what i meant and what i wanted to do.
>
> Having trouble figuring out why the #REF
>
> i double checked the validity of the ranges using F5 and also in Insert ->
> Name -> Define: they're all good.
>
> there's got to be something else i'm not seeing
>
> thanks again Tom, you're awesome!
>
> J
>
> "Tom Ogilvy" wrote:
>
> > Assume R2 contains the textstring
> >
> > July
> >
> > and you have a table of values named July_A2, then
> >
> > Indirect(R2 & "_A")
> >
> > would return a reference to that table of values which Vlookup is
looking
> > for as the second argument.
> >
> > That is what I understood you to say the situation is. Using the
situation
> > I described, it works fine for me.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > news:BCA131CF-2DF3-4652-B1B9-F54F54F52C5C@microsoft.com...
> > > Hi Tom,
> > >
> > > does it matter that
> > > Indirect(R2&"_A") refers to a range larger than just one cell?
> > > i'm getting a #REF error on
> > > Indirect(R2&"_A")
> > >
> > > J
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > =If(len(r2),vlookup(O4,Indirect(R2&"_A"),2, false),"")
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > > > news:925039FE-4F03-4D7D-A5A3-D26BA6573F68@microsoft.com...
> > > > > Hi all,
> > > > >
> > > > > i'm trying to construct a formula that will look like this in
excel
> > > > > =vlookup(O4,Month_A&"_A",2, false)
> > > > >
> > > > > Month_A (R2) contains a drop-down data validation with a list of
> > months
> > > > > the user selects the month and then the formula takes that
> > > > > appends _A to it
> > > > > and that will be the name of the range it will lookup in
> > > > >
> > > > > doing it the way i am, produces a string that apparently excel
doesn't
> > > > like,
> > > > > or know what to do with.
> > > > >
> > > > > is there a way to convert the string back to a reference that
excel
> > will
> > > > > understand?
> > > > >
> > > > > tia
> > > > >
> > > > > J
> > > >
> > > >
> > > >
> >
> >
> >