Hi!
Good Morning!
While working with the case, I realised that combination of both - excel formula and macro - can solve my problem effectively.
Hence, I've simplified the case with the help of formula. Almost half of the issue has been resolved, but the remaining half requires VBA to solve, because...(I'll explain the reason at last)
The simplified case is:
There are only 3 range - (F74:F81), (X74:X81), and (H40) to work with.
1. Range F74:F81 is linked with other cells, hence dynamic. Means, it may have any text.
2. Range X74:X81 can have only 2 value in each cell - either 1 or Empty ("") - set by formula.
3. Range H40 will be used for showing custom remark based on the value of range F74:F81 and X74:X81.
Annotation 2020-09-21 022825.jpg
Now the condition is,
(1) If Range X74:X81 is empty, Range H40 will show - "No Remarks"
(2) A) If Range X74 = "1" and all other cells are empty (X75:X81="") then, Range H40 will show "decline in (Corresponding cell value in Range F74:F81)" For example - decine in English
(There will be 8 condition when the Range X74:X81 will be empty except one cell which will contain 1.
B) Similarly, if there are two cells having value 1 in each, and remaining six cells are empty, Range H40 will show - "decline in (Corresponding cell value1) and (Corresponding cell value2)
....
....
....AND SO ON......
In short,
Whenever value "1" is appear in the Range X74:X81, and all the other cells are empty ("") in the same range, the corresponding value in the Range F74:F81 should be shown in Range H40 as remark, in the format - "decline in value1,value2,...value(n-1) and value(n)"
Important: Similarly if we extend this condition when there will be value 1 in the cell(s), and other cell(s) are empty, we got a total unique combination 8x7x6x5x4x3x2x1= 8! = 40320 conditions. No any excel formula is there to satisfy such a big number of conditions, as I know. This is the reason I'm looking for VBA.
I'm attaching here the sample file.
Reply With Quote
Bookmarks