Adding text to a column based on value in another column

[exceluser29]

Hi,

I have two tables in hand:
Table 1: has data showing Customer no, Renewal Month, last_active_date, report_pull_dt and other columns.

Table 2: has Renewal Month and Cancel Dt Limit

What I have to do is add a column in table 1 (Status) based on their last_active_date .

For example, If renewal Month is July and last_active_date < Cancel Dt Limit, then status = 'cancel' else 'Active'.

I will have to lookup the Cancel Dt Limit from table 2, based on their renewal month.

Can someone please help? I was thinking a combination of Vlookup and if statement but dont know how to write!

Thanks!

 

[DonkeyOte]

Perhaps (if I've understood):

Excel Workbook
	A	B	C	D	E
1	Customer No	Renewal Month	Last Active Date	Report Pull Date	CustStat
2	1	Jan	15-Jul-10	20-Jul-10	Active
3	2	Feb	13-Jun-10	20-Jul-10	Active
4	3	May	13-Jun-10	20-Jul-10	Cancel
5	4	Nov	13-Jun-10	20-Jul-10	Active
Sheet1

Note: given the rule re: 2 months - the above is no longer using the reference table (the date logic is built into the formula directly)

 

[exceluser29]

Thanks for your reply! I tried the formula in my sheet but for some reason, it is giving me 'Active' for all cells. I am not getting 'Cancel' for cases like you have in row4. Any suggestions? what am I doing wrong?

 

[DonkeyOte]

Are the values in Col C really date values - ie not text strings ... ?

What does

=COUNT(Sheet1!C:C)

return (adjust references accordingly per your real file)

 

[exceluser29]

Hmm..I think it is formatted as 'general'. The COUNT(Sheet1!C:C)
returned 0 (adjusted to my sheet).

 

[DonkeyOte]

OK this means the values in C are dates stored as text and text > number

Assuming you don't want to adjust the original values (you can if you want to) you can try coercing the values in the formula:

=IF(OR(MONTH(1&$B2)>MONTH($D2),($C2+0)>DATE(YEAR(TODAY()),MONTH(1&$B2)+2,15)),"Active","Cancel")

the change above is that we add 0 to C2 - the act of addition coerces the string to number - if the date strings are invalid the above will generate #VALUE! errors.

 

[exceluser29]

Awesome! This worked like a charm. You rock!

Thanks so much for your patience!