| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I am looking for formula to calculate escalation on construction | Excel Discussion (Misc queries) | |||
| formula construction | New Users to Excel | |||
| Efficient Array Formula Construction | Excel Discussion (Misc queries) | |||
| formula construction | Excel Discussion (Misc queries) | |||
| formula construction | Excel Programming | |||
![Gixxer_J_97[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
