Can you attach a simple example workbook so we can see all of this in context? This will be possible with formulas (probably array formulas), however a different design might improve things.
Just to pick up on the earlier discussion regarding VBA and OO programming.
VBA supports all of the main OO concepts except for inheritance. However, you will find a general trend (an exception, for example, is when working with userforms) that VBA developers prefer to work with a procedural-driven approach rather than a class-driven approach. This is due to state loss events. A state loss event will reset all static and module level variables, and it will destroy all the custom class objects you have created. I'm not aware of a complete list of state loss causes, but here's a starting point: http://www.xtremevbtalk.com/showpost...60&postcount=1
Getting off topic here, but Colin - are you seriously saying that VBA supports polymorphism?
I'm happy to argue Encapsulation too, but it's less black and white.
I apologise if my post was misleading - VBA does not support true polymorphism; I 'justify' my not excluding it because VBA does support interface polymorphism to a limited extent (obviously no overloading etc..). And abstraction and encapsulation in VBA are both ticked boxes for me.
This thread is a follow on from another one where Mike and I were discussing constructors and overloading in VBA.