If function query

[Dave350z]

Ok folks, first time post, so please bare with me...

I have two tabs, a sales tab and a cancel tab...

On both tabs I have a unique reference for a Customer, which matches both, a customer has a sale today and will appear on the sales report, they cancel tomorrow and will appear on the cancel report (both with the same unique number).

Question arises when the same customer then reopens their account and I have another sale, so they are effectively active again.

What I want to show is, on the cancel report, whether or not the customer is still active or not?

I have a date stamp in each report, so the sale can come through with yesterday's date, it can come in on the cancel report today and then come back in on the sales report tomorrow.

Any ideas on how I could update the cancel report to show whether or not the customer was active or not?

Apologies if it's a little vague.

Thanks in advance.

Dave

[Richard Buttrey]

Hi,

Welcome to the forum.

Could you upload your workbook (or a cut down version of it), and manually add the results you expect to see for all permutations of inputs.

[Dave350z]

Attached is a simple version, on the sales tab, there are two sales, both with different dates.

On the cancel tab, there is a cancel date, I have highlighted the cells I want to formulate with an Active/Inactive formula.

In this instance, there is a sale, then a cancel and then a new sale, all for the same customer, so this customer is still active.

Cheers
Dave

[Richard Buttrey]

Hi,

Maybe

=IF(COUNTIF(Sales!A:A,Cancelled!A2)-COUNTIF(Cancelled!A:A,Cancelled!A2)>0,"Yes","No")

[Dave350z]

Thanks Richard, it works for this one, but the only issue I have is, it is possible that I do not have the original sale (It may have been 5 years ago and is not on my current file), so I only have 1 sale and 1 cancel, so using the above formula, it would show as "No", even though there was a sale taken out after the last cancellation date... so it probably needs to be date driven rather than count driven if that makes sense?

Thanks for the replies though!

[Richard Buttrey]

Hi,

In that case how about

=IF(IF(Sales!A2:A10=Cancelled!A2,MAX(Sales!B2:B10),"")>B2,"Yes","No")

change 2:10 as appropriate

[Dave350z]

Perfect Richard, so it basically looks up values in sales and cancels, if they match, it takes the MAX (or newest in this case) date from the sales tab and IF that value is greater than the cancel date, then it's active!

[Dave350z]

Hey folks, a little bit of a thread resurrection, the formula worked fine as I was only testing ONE customer, but when I eventually got around to applying it to my file, it came back with only results based on the first cell in the cancelled tab...

I have attached an excel sheet with the formula above in it, highlighted in YELLOW in the cancelled tab and then wrote beside it, what the formula should have returned.


(I think the problem has something to do with looking at the unique values between each sheet, it's only returning the values based on the first cell in the cancelled tab)

Cheers
Dave

[Dave350z]

Any insights would be greatly appreciated...cheers

[Richard Buttrey]

Hi,

If you're happy to sort the Sales sheet by column B ascending, then in C2 on the Cancelled sheet and copied down

=IF(IFERROR(INDEX(Sales!B:B,MATCH($A2,Sales!$A:$A),1),INDEX(Sales!B:B,MATCH($A2,Sales!$A:$A,FALSE),1))>B2,"Yes","No")