Hi there,
I have a larger client model with several sheets representing a different product for clients. One client might have several products and thus be in each sheet. We (at work) use the model to renew the clients and we have to change status of each client. For example when a clients quits we have to mark it as expired. But sometimes the clients just stops having one product and keep other products. And this is where I need help. Because I have one sheet that shows all clients with each product. So whenever I change status to "expired" for one product it stands "expired" for all products even they still have those products. At the "all products" sheet where all clients is listed with all the products I have a formula that finds which status the client is. It looks like this:
So if the status is "expired" for Product1 and "active" for Product2 it is registered as being expired.
Does anybody know how I can work around this problem?
I have a larger client model with several sheets representing a different product for clients. One client might have several products and thus be in each sheet. We (at work) use the model to renew the clients and we have to change status of each client. For example when a clients quits we have to mark it as expired. But sometimes the clients just stops having one product and keep other products. And this is where I need help. Because I have one sheet that shows all clients with each product. So whenever I change status to "expired" for one product it stands "expired" for all products even they still have those products. At the "all products" sheet where all clients is listed with all the products I have a formula that finds which status the client is. It looks like this:
Excel Formula:
=IF(IFERROR(INDEX(Product1!$C$5:$C$486;MATCH(B3;Product1!$A$5:$A$486;0));"")<>"";INDEX(Product1!$C$5:$C$486;MATCH(B39;Product1!$A$5:$A$486;0));IF(IFERROR(INDEX(Product2!$C$5:$C$498;MATCH(B3;Product2!$A$5:$A$498;0));"")<>"";INDEX(Product2!$C$5:$C$498;MATCH(B3;Product2!$A$5:$A$498;0))....."")))))))
So if the status is "expired" for Product1 and "active" for Product2 it is registered as being expired.
Does anybody know how I can work around this problem?