Correct status

[Even]

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:

=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?

 

[awoohaw]

@Even , can you post a mini worksheet using the xl2bb add in? Just small sample. You can dummy up the data for anonymity. Hide columns that are not part of the calculation. You can even put dummy data from different sheets in the same sheet view to make it easier, just make a comment about that.

 

[Even]

Model test.xlsx
	A	B	C	D
1	Client	Status
2	Client A	Renewed		Product 1
3	Client B	Expired
4	Client C	In process		Product 1
5	Client D	Renewed		Product 1
Product1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(OR(B2="Expired",A2=""),"","Product 1")

Model test.xlsx
	A	B	C	D	E
1
2	Status	Client	Product 1	Product 2	Product 3
3	Renewed	Client A	ü
4	Expired	Client B		ü	ü
5	In process	Client C	ü	ü	ü
6	Renewed	Client D	ü	ü	ü
All products

Cell Formulas
Range	Formula
C3:C6	C3	=IF(COUNTIFS(Product1!$A$2:$A$500,$B3,Product1!$D$2:$D$500,$C$2)>0,"ü","")
D3:D6	D3	=IF(COUNTIFS(Product2!$A$2:$A$500,$B3,Product2!$D$2:$D$500,$D$2)>0,"ü","")
E3:E6	E3	=IF(COUNTIFS(Product3!$A$2:$A$500,$B3,Product3!$D$2:$D$500,$E$2)>0,"ü","")
A3:A6	A3	=IF(IFERROR(INDEX(Product1!$B$2:$B$500,MATCH(B3,Product1!$A$2:$A$500,0)),"")<>"",INDEX(Product1!$B$2:$B$500,MATCH(B3,Product1!$A$2:$A$500,0)),IF(IFERROR(INDEX(Product2!$B$2:$B$500,MATCH(B3,Product2!$A$2:$A$500,0)),"")<>"",INDEX(Product2!$B$2:$B$500,MATCH(B3,Product2!$A$2:$A$500,0)),IF(IFERROR(INDEX(Product3!$B$2:$B$500,MATCH(B3,Product3!$A$2:$A$500,0)),"")<>"",INDEX(Product3!$B$2:$B$500,MATCH(B3,Product3!$A$2:$A$500,0)),"")))

 

[Even]

Does this make sense ?

 

[awoohaw]

Does this make sense ?

just saw it. will comment in a while.

 

[awoohaw]

can you post examples of the Product sheets?
I'm not really sure what youre trying to accomplish by the formulas.

 

[Even]

Thanks for looking at it. I have removed a column as it's not crucial to the problem I am facing. In each product sheet there are clients informing us what status they have, for example if they have renewed, in the process of renewing or if the client long has the product. And in another sheet (All products) we have all the clients accumulated showing the same status in column A.

This is from the sheet "All products" where it informs that Client B is expired and no longer needs to be renewed.

Model test.xlsx
	A	B
2	Status	Client
3	Renewed	Client A
4	Expired	Client B
5	In process	Client C
6	Renewed	Client D
All products

Cell Formulas
Range	Formula
A3:A6	A3	=IF(IFERROR(INDEX(Product1!$B$2:$B$500,MATCH(B3,Product1!$A$2:$A$500,0)),"")<>"",INDEX(Product1!$B$2:$B$500,MATCH(B3,Product1!$A$2:$A$500,0)),IF(IFERROR(INDEX(Product2!$B$2:$B$500,MATCH(B3,Product2!$A$2:$A$500,0)),"")<>"",INDEX(Product2!$B$2:$B$500,MATCH(B3,Product2!$A$2:$A$500,0)),IF(IFERROR(INDEX(Product3!$B$2:$B$500,MATCH(B3,Product3!$A$2:$A$500,0)),"")<>"",INDEX(Product3!$B$2:$B$500,MATCH(B3,Product3!$A$2:$A$500,0)),"")))

According to the sheet "Product 1" this is correct - the client no longer has Product 1:

Model test.xlsx
	A	B
1	Client	Status
2	Client A	Renewed
3	Client B	Expired
4	Client C	In process
5	Client D	Renewed
Product1

However, Client B has Product 2, which is renewed:

Model test.xlsx
	A	B
1	Client	Status
2	Client A	Expired
3	Client B	Renewed
4	Client C	In process
5	Client D	Renewed
Product2

So I just need help with the fomula in the sheet "All products" to reflect the correct status (in column A). This problem occurs when a client has more than one product and the status is different for the products; let's say renewed for product 1, but expired for product 2.


Hopefully this helps

 

[awoohaw]

Thanks, but I needed all product sheets that are referenced in your AllProducts sheet. The formulas break without those references. You can test this for yourself by doing a copy from ALLPRODUCTS sheet above (not your workbook as copy may remember the workbook source), and paste into a brand new workbook. You'll get a rude dialog box that bascially says open a file, and I don't have that file. You do on your computer!

But, as I understand it without any kind of excelling you want a status of clients based upon statuses and the structure of your product tables make it hard to do.
So, my suggestion would be to set up a hierarchy of what status does the aggregate statuses of all products mean for that client.
If any of the 3 are "in process" then the client status is "in process",
Or if renewed is more important then renewed should be the overall status.

So, without me giving formulas... you seem to have a grasp of that, so you can build them based on this:
If OR(Product1Status = "Renewed",Product2Status = "Renewed",Product3Status = "Renewed") then "Renewed",
If Or(Product1Status = "In Process",Product2Status = "In Process",Product3Status = "In Process") then "Renewed"
If Or(Product1Status = "Expired",Product2Status = "Expired",Product3Status = "Expired"), then "Expired"

if a Client isn't on a particular product sheet you'll have to create some kind of default (I suggest "Expired" because the first two steps will overrule that anyway.

But frankly, and I know this is not a database. But associating the status of a Client based upon product like this goes against rules of database normalization.
My suggestion is to have a separate client worksheet for status with the client status and just have single massive product sheet where you record both all clients and all products together without status.

If you need to record that a product has been discontinuted, for that client, then set up a product status column or delete from the worksheet.

ClientA	Expired
ClientB	In Process
ClientC	Renewed

ClientA	Product1
ClientA	Product2
ClientB	Product1
ClientB	Product3
ClientC	Product1
ClientC	Product2
ClientC	Product3