Excel is a very powerful product from Microsoft that helps people around the world make budgets, create forecasts and evalaute financial information of all kinds. It's also a great text processor. It turns out that Excel has a few undocumented formulas that have been deprecated, but that still work in Excel 2010 for backwards compatibility. One of them is not a function at all, but rather a macro that can be evalauted from named ranges only (aka 'names') in Excel. This macro is called EVALUATE(). You must enable macros to use it. Click below to see a full description of how this can be used to dynamically create formulas.
EVALUATE() is the most strange Excel function in that it lets you take a string and literally make Excel evalaute the string and convert the string to an Excel Formula. The contents of a cell referring to the named range that calls EVALUATE(...) then spits out the result of this formula.
What does this give you? The ability to dynamically create Excel formulas using a variety of rules and then evaluate the resulting formulas in Excel. You can write a formula generator based on various rules, create the formula as a text string and then use the result in Excel. Click on the screen shot below to see the full explanation.
IMPORTANT NOTE: The =Result cell above DOES not recalculate if the underlying worksheet values (2001,2002,2003) change. It only recalculates when =Result is reentered or if the dynamic formula in Sheet1!A1 is changed or just reentered. Normal Excel recalcs do not affect this because EVALUATE() is not a regular Excel formula, but rather a macro call. And you must have macros enabled for this to work.