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

formula construction w/ vba
 

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

--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
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

formula construction w/ vba
 

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" wrote in message
...
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

formula construction w/ vba
 

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" wrote in message
...
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" wrote in message
...
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

formula construction w/ vba
 

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" wrote in message
...
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" wrote in message
...
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

formula construction w/ vba
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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