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.