06-15-2020 07:56 AM
06-15-2020 07:56 AM
I have a formula below (and attached in column Q of 'LIVE' tab)
but I need to build in an additional statement. Regardless of whether a contract is between the date range above, if a client is ACTIVE in a given year (by looking up the same Account name in the 'ACTIVE' tab and if it says 'ACTIVE' in 2017, then I want it to say LIVE anyway regardless of the dates.
For example in column Q for account name Zista - this should say LIVE, even though the contract did not fall within the dates, if it is ACTIVE, then it must be LIVE no matter what.
In Summary, if an account is ACTIVE, then "LIVE" else go by original formula above.
Thank you in advance for your help it is much appreciated!
06-15-2020 06:06 PM
@JennySommet follwing the summary you provided "if an account is ACTIVE, then "LIVE" else go by original formula above"
You might be looking to construct a nested IF logic containing an HLOOKUP function as shown below;
Added formula = IF(HLOOKUP(A2,ACTIVE!$E$4:$I$395,2) = "Active", "LIVE", Original_Formula_Above)
- Original formula = IF(AND(YEAR(AA$1)>=YEAR($F2),YEAR(AA$2)<=YEAR($G2),DATE(Q$1,1,1)<>$G2),"LIVE","")
When put together, you should have your final formula for Column Q to look something like this:
=IF(HLOOKUP(A2,ACTIVE!$E$4:$I$395,2) = "Active", "LIVE", IF(AND(YEAR(AA$1)>=YEAR($F2),YEAR(AA$2)<=YEAR($G2),DATE(Q$1,1,1)<>$G2),"LIVE",""))
And because we are looking at the 2017 in the ACTIVE sheet the hlookup function has the number 2(2nd column in table in the ACTIVE sheet). If we are to enter the same formula for 2018, the hlookup function would have the number 3(3rd column in table in the ACTIVE sheet.
06-16-2020 01:56 AMSolution
@JennySommet I am quite certain that the HLOOKUP function above will not work. However, using VLOOKUP like this will do what you are asking for.
=IF(VLOOKUP(A2,ACTIVE!$A$1:$E$392,2,FALSE) = "Active", "LIVE",<original formula>)
But, I can't resist indicating that I believe your original formula is overly complicated. There is no need for the helper dates in AA and further. In, the attached file, I have made some changes in most of your formulae. Furthermore, I chose a combination of INDEX and MATCH in stead of VLOOKUP. Note that I used "LIVE-1" and "LIVE-2" in the formulae in columns Q:S to make it visible which "LIVE" the formula returns. The one from the INDEX function or the one from the second IF function. Once you are satisfied that this is correct you can change both to "LIVE".
It's your choice of course, if you want to use these changes or not.
06-17-2020 03:40 AM
Thank you! I'm afraid I need to attach original report however, as I cannot seem to transfer the formula across correctly? I am happy to use either formula. As you can see from the excel, the 'Active' Tab is actually called 'Indirect' and has further info there. The report is an absolute nightmare, but this formula you have provided helps SO MUCH!
06-17-2020 03:49 AM
06-17-2020 06:37 AM
@JennySommet See attached. Must agree that this is not the most user friendly schedule I have seen. And I have not gone into the details. Just focussed on the formulae in columns Q:T on the "Salesforce" sheet. Please do check if this is indeed what you want.
06-19-2020 03:22 AM