using a "variable" in range names

[Adam Kroger]

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

 

[Bob Phillips]

Can't see it without INDIRECT.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Adam Kroger]

I tried VLOOKUP("bongo", INDIRECT(CONCATENATE("rn",A1,"mw")), 0) but that
came back with a NAME? error.

 

[Arvi Laanemets]

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

 

[Bob Phillips]

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]

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

 

[paul]

i have a similar quandary,would it work if you allow labels in
formulas???(tools options,calculation,tick allow labels in formulas)

 

[Arvi Laanemets]

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]

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.

 

[Adam Kroger]

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.