Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
Can you us a "variable" in a range name without having to INDIRECT() to
another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
Can't see it without INDIRECT.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
I tried VLOOKUP("bongo", INDIRECT(CONCATENATE("rn",A1,"mw")), 0) but that
came back with a NAME? error. "Bob Phillips" wrote in message ... Can't see it without INDIRECT. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
Hi
=VLOOKUP("bongo",CHOOSE(A1,rn1mw,rn2mw,rn3mw,rn4mw ,rn5mw),2,0) NB! in your original formula, you have 3rd parameter for VLOOKUP equal to 0. It is a nonsense - yo are asking to return the value from no column. But having 4rd parameter equal to 0 has a real meaning. Arvi Laanemets "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
You need a column offset
=VLOOKUP("bongo", INDIRECT(CONCATENATE("rn",A1,"mw")),2, FALSE) or 3 or 4, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message . .. I tried VLOOKUP("bongo", INDIRECT(CONCATENATE("rn",A1,"mw")), 0) but that came back with a NAME? error. "Bob Phillips" wrote in message ... Can't see it without INDIRECT. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
In my first & second generations of it I did use the CHOOSE() to find the
range I wanted, but I need the formula to be shorter. It is part of a 6 or 7 nested IF formula, that was running up against the character limit for an excel formula, not to mention monumentally difficult to debug. the sample I used was just a sample, the actual one has a MATCH() as teh 3rd argument in the VLOOKUP(). Thanks "Arvi Laanemets" wrote in message ... Hi =VLOOKUP("bongo",CHOOSE(A1,rn1mw,rn2mw,rn3mw,rn4mw ,rn5mw),2,0) NB! in your original formula, you have 3rd parameter for VLOOKUP equal to 0. It is a nonsense - yo are asking to return the value from no column. But having 4rd parameter equal to 0 has a real meaning. Arvi Laanemets "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
i have a similar quandary,would it work if you allow labels in
formulas???(tools options,calculation,tick allow labels in formulas) -- paul remove nospam for email addy! "Bob Phillips" wrote: Can't see it without INDIRECT. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
Hi
Maybe you simply give us your original setup, and what you want to do with it. Mostly such complex solutions are needed at all - they are results of wrong planning at earlier stages. And when you can't avoid complex formula, you almost always can split it into several subformulas, and define them as named formulas - thus shortening your final formula drastically. An example: MyRange= CHOOSE($A$1,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw) MyCol=MONTH(TODAY())+1 =VLOOKUP("bongo",MyRange,MyCol,0) Arvi Laanemets "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message . .. In my first & second generations of it I did use the CHOOSE() to find the range I wanted, but I need the formula to be shorter. It is part of a 6 or 7 nested IF formula, that was running up against the character limit for an excel formula, not to mention monumentally difficult to debug. the sample I used was just a sample, the actual one has a MATCH() as teh 3rd argument in the VLOOKUP(). Thanks "Arvi Laanemets" wrote in message ... Hi =VLOOKUP("bongo",CHOOSE(A1,rn1mw,rn2mw,rn3mw,rn4mw ,rn5mw),2,0) NB! in your original formula, you have 3rd parameter for VLOOKUP equal to 0. It is a nonsense - yo are asking to return the value from no column. But having 4rd parameter equal to 0 has a real meaning. Arvi Laanemets "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
I think I have the variable for the named ranges worked out in-so-far as
calling them from within the active worksheet. I could reduce the formula, if I could figure out a way to have a formula called by a VLOOKUP(MATCH()) be executed from the cell that performed the lookup. Something along the lines of: =IF(NOT(A20="P"), IF(ISTEXT(C20), IF($C$5="IS_weap_list", VLOOKUP(C20, IS_weap_table, MATCH("Dam", IS_weap_head, 0), 0), IF($C$5="CL_weap_list", VLOOKUP(C20,CL_weap_table, MATCH("Dam", CL_weap_head,0), 0), "")), ""), IF($C$5="IS_weap_list", UseSameAS(VLOOKUP(C20, IS_weap_table, MATCH("Dam", IS_weap_head, 0), 0)), IF($C$5="CL_weap_list", UseSameAS(VLOOKUP(C20, CL_weap_table, MATCH("Dam", CL_weap_head,0), 0))))) with =IF(ISNUMBER(mechtons),3*ROUNDUP(mech_tons/10,0),"") residing in the retreived cell. mechtons is a named range that contains unique data for each sheet There are 8 possible returns that will get triggered by the A20="P" test, each of wich have a unique related formula that must be executed using data specific to calling sheet, all the other returns are a whole number. UseSameAS() is a UDF that I pulled from: http://www.mvps.org/dmcritchie/excel/formula.htm I have tried all 3 variations for runnign another cells formula that are shown on that page, but none of them have run right. If you want to look at the actual workbook, I'd be happy to email it to you, but unless you are familiar with a wargame called Battletech, you probably won't be able to follow what I am trying to do. "Arvi Laanemets" wrote in message ... Hi Maybe you simply give us your original setup, and what you want to do with it. Mostly such complex solutions are needed at all - they are results of wrong planning at earlier stages. And when you can't avoid complex formula, you almost always can split it into several subformulas, and define them as named formulas - thus shortening your final formula drastically. An example: MyRange= CHOOSE($A$1,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw) MyCol=MONTH(TODAY())+1 =VLOOKUP("bongo",MyRange,MyCol,0) Arvi Laanemets "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message . .. In my first & second generations of it I did use the CHOOSE() to find the range I wanted, but I need the formula to be shorter. It is part of a 6 or 7 nested IF formula, that was running up against the character limit for an excel formula, not to mention monumentally difficult to debug. the sample I used was just a sample, the actual one has a MATCH() as teh 3rd argument in the VLOOKUP(). Thanks "Arvi Laanemets" wrote in message ... Hi =VLOOKUP("bongo",CHOOSE(A1,rn1mw,rn2mw,rn3mw,rn4mw ,rn5mw),2,0) NB! in your original formula, you have 3rd parameter for VLOOKUP equal to 0. It is a nonsense - yo are asking to return the value from no column. But having 4rd parameter equal to 0 has a real meaning. Arvi Laanemets "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a "variable" in range names
I got it to work by using INDIRECT("rn"&A1&"mw")
I'm not sure what teh "allow labels" does. But it is checked in my excel. "paul" wrote in message ... i have a similar quandary,would it work if you allow labels in formulas???(tools options,calculation,tick allow labels in formulas) -- paul remove nospam for email addy! "Bob Phillips" wrote: Can't see it without INDIRECT. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you us a "variable" in a range name without having to INDIRECT() to another cell? -facts- A1 has a number in it rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges -problem- I want to use a vlookup to one of the above ranges. Something like: VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0) or VLOOKUP("bongo", "rn"&A1&"mw", 0) of course those formulas do not work..... thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
not delete worksheets from names in a range | Excel Discussion (Misc queries) | |||
how do i create range names ? | Excel Worksheet Functions | |||
Range Names | Excel Worksheet Functions | |||
Excel Range Names | Excel Discussion (Misc queries) | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |