Showing Custom Value Based on the Condition of Dynamic Table

[DocAElstein]

Hi,
I suspect it could still be possible to achieve all you want with a formula based on some clever VLookUp and / or Index
I personally am unable to do that, - its beyond my formula abilities.
( see also note below *** )

---

So, to the VBA solution for your current situation
Your current situation is at a point similar to My initial thinking ( https://excelfox.com/forum/showthrea...ll=1#post14910 )
But you have now already got effectively that yellow column of 1’s – obtained from your formula.

So we already have close to the macro you need . – It is close to the first macro here , https://excelfox.com/forum/showthrea...ll=1#post14912 , the main difference being that now we don’t need to produce the arrDec() via logic in coding, - we can simply bring it in from the column that your formulas produce, X74:X81

If I understand correctly, you have fixed now your subject range size, so we don’t need to determine the number of subjects . If this is not the case, then the macro can be easily retuned to being dynamic to allow for any number of subjects, as it was previously.
We no longer need to consider the data range ( now K74:S81 ) in as much detail as previously , since all workings on that is now done by your formulas. But I assume the macro should be set off by changes in the range K74:S81

So this would be my next macro offering: ( I have modified the previous macro, so I leave you to “tidy it up” as you choose )
Macro is here: https://excelfox.com/forum/showthrea...ll=1#post14957
I can’t test it thoroughly since your formulas do not work in my Excel versions***
( I use Sub Testie() to test my macro. This Sub Testie() simulates a change in your data range , K74:S81 . I can’t actually test in my Excel by changing a value in your data range, because that causes an error in your formula. – Your formula does not work in any of my Excel versions *** )

Alan

---

*** Important note: Your formulas are using the Excel function IFS
Your formula does not work in any of my Excel versions. ( I believe the Excel function IFS is only available since Office 2016 ).
If you share your formula solution to anyone with an earlier version of Office, then it won’t work!!!!
( I use Excel 2002, 2003, 2007 and 2010 only )

[Anshu]

Wow! THANKS for RE-Modification of the macro!
This is exactly what I was asking for. It works without any issue. I've modified the range according to my original table and "LCase" to "Application.Proper" to obtain subject case in proper case.
This code seems easy to understand. Also, this macro helps me to understand the previous macro which was complex for me till morning!


Thank you so much!!

Finally SOLVED!!