since I already know how powerful your brains are I would like to kindly ask you for extra help with building a macro to help me to run a specific evaluation of interactions btw different categories (in this case A - I).
Here, I also include the WB where I need to make this happen:
What I need the macro to do is the following:
I have an input table that looks like this:
I want to use the checkboxes to do the following for me:
when unchecked - the effect of relevant category will be 0
when checked - the macro will scan the array with checkboxes (in my case column Q) and will search for other checked boxes. Two possibilities may appear:
1) there are no other checked categories and the intensity of the category effect will remain full
2) there are other checked boxes and the macro will look into a predefined matrix (in my case sheet InteractionMatrix) and will look if there is an interaction between the box I checked and other checked boxes / categories within the array.
2.1) if there is no interaction (represented by value 0) - intensity remains unchanged
2.2) if there is an interaction (represented by value 1) - intensity will be adjusted according to relevant values that the macro will search for in matrix in sheet ValueMatrix) - multiplier will be shown in the sheet Summary (2) column P (attributed to relevant category).
I think it's a lot of if statements combined together essentially that need to be coded.
The hard part is that the interaction effect should translate also to the category with which our "checked" category interacts - say A interacts with B - and if B is checked it should assume for the same interaction value.
This may be still easy to do BUT we know there are multiple interactions between the categories and so if I have checked A, B, E and they all interact - the effect should be recorded in the column P for all of them.
The interaction functionality may be attributed to a command button - however would be nice if could be automatic - whatever is easier though in terms of sequences of calculations.
I hope this post is clear and the goal with it.
I appreciate your help of any kind!
I hope we get this riddle solved!
Thank you in advance.