Hi Gents/Ladies,
With regards to the implementation of the common reporting standards, i would like to workout a workbook which allowed for my easy monitoring as example set below, would be appreciate if anyone could help me to workout a formula to satisfy the following rules:
As per above table, would be appreciate if anyone could help me draft the formula to obtain the column at result.
As for the jurisdiction country - i prefer the formula could look up into a list whereby i can update from time to time. (example above, i will be preparing a side list where only Hong Kong is detectable)
Says if the entity type fall under "Financial Institution" or "Active NFE", regardless of the jurisdiction variables, the end result should be "Non-Reportable"
When the entity type fall under passive NFE, the formula could look up the corporate jurisdiction and CP1 jurisdiction from a list, if the lookup criteria fall as shown below, result should return as shown below:
Example:
I am having a side list which only contain Hong Kong.
Notes 1: formula identify if entity type is passive NFE, to lookup both corporate and cp1 jurisidiction from the list prepared
Notes 2: if both corporate jurisdiction and CP1 jurisdiction country match the list - the result to return = CRS101
Notes 3: if corporate jurisdiction match the list, but CP1 jurisdiction not match the list - the result to return = CRS103
Notes 4: if corporate jurisdiction not match the list, but CP1 jurisdiction match the list - the result to return = CRS101
Notes 5: if both corporate and cp1 jurisdiction not match the list - the result to return = Non-Reportable
Apologies for the complex variables and the long essay writing.
Would be very much appreciated if anyone could advise on a workable formula to cover all of the above criteria.
Thanks and stay safe.
With regards to the implementation of the common reporting standards, i would like to workout a workbook which allowed for my easy monitoring as example set below, would be appreciate if anyone could help me to workout a formula to satisfy the following rules:
| Entity Type | Corporate Jurisdiction | CP1 - jurisdiction | Result (formulated) |
| Financial Institution | Taiwan | Hong Kong | Non-Reportable |
| Financial Institution | Hong Kong | Taiwan | Non-Reportable |
| Active NFE | Taiwan | Taiwan | Non-Reportable |
| Active NFE | Hong Kong | Hong Kong | Non-Reportable |
| Passive NFE | Taiwan | Hong Kong | CRS101 |
| Passive NFE | Hong Kong | Taiwan | CRS103 |
| Passive NFE | Taiwan | Taiwan | Non-Reportable |
| Passive NFE | Hong Kong | Hong Kong | CRS101 |
As per above table, would be appreciate if anyone could help me draft the formula to obtain the column at result.
As for the jurisdiction country - i prefer the formula could look up into a list whereby i can update from time to time. (example above, i will be preparing a side list where only Hong Kong is detectable)
Says if the entity type fall under "Financial Institution" or "Active NFE", regardless of the jurisdiction variables, the end result should be "Non-Reportable"
When the entity type fall under passive NFE, the formula could look up the corporate jurisdiction and CP1 jurisdiction from a list, if the lookup criteria fall as shown below, result should return as shown below:
Example:
I am having a side list which only contain Hong Kong.
Notes 1: formula identify if entity type is passive NFE, to lookup both corporate and cp1 jurisidiction from the list prepared
Notes 2: if both corporate jurisdiction and CP1 jurisdiction country match the list - the result to return = CRS101
Notes 3: if corporate jurisdiction match the list, but CP1 jurisdiction not match the list - the result to return = CRS103
Notes 4: if corporate jurisdiction not match the list, but CP1 jurisdiction match the list - the result to return = CRS101
Notes 5: if both corporate and cp1 jurisdiction not match the list - the result to return = Non-Reportable
Apologies for the complex variables and the long essay writing.
Would be very much appreciated if anyone could advise on a workable formula to cover all of the above criteria.
Thanks and stay safe.
