06-22-2020 05:32 AM
06-22-2020 05:32 AM
This is very complex! In columns U-X of attached excel, I want the result to show the contract type of the account (column H), but it should also match the contract type it was THAT YEAR. I don't know if this is possible. Each Account in column A has several contracts - hence the multiple lines. I want columns U-X to always show the contract type in a given year, regardless of what the start and end date on the specific line is. I hope this makes sense! This report is driving me insane
I have put the correct result it should show below.
Thank you so much!
06-22-2020 06:04 AM - edited 06-22-2020 06:04 AM
Have you try to make another column with CONCATENATE ? You could have in this new column the contract type & the year of the contract, so that you have a new unique information, that you could match with the same column created in your database?
06-22-2020 06:32 AM
06-22-2020 08:14 AM
Hey friend, Can you explain the issue in more details as above nothing is clearly understood. Also please try connecting with me then I would explain or solve your issue@JennySommet
06-22-2020 08:38 AM
Hi Jenny (Again!)
I am not sure I understand completely what you want but has attached a workbook with some cells shaded in green which have a vlookup formula. The answers i get don't match with yours so maybe it is not correct? have a look anyway.
06-22-2020 09:35 AM
Oh hi @peteryac60 !
I'm 100% sure you can help me, but I'm finding it so difficult to explain what I need from the report (which is currently causing a lot of stress as no matter what I do, it does not provide the results I need due to the complexity of all the rules). Maybe I should start from scratch?
I need 4 columns:
In each column it must say either EC or Agent. The only way it can know which one, is by the start and end dates. If the start and end date ran across 2017, then I would want 2017 to show the contract type that year. Only exception to the rule, is, if a contract was 'Active' that year (you can only tell by going into the 'indirect tab), then I would want it say EC or Agent even if the start and end dates did not correspond. It is really very hard to explain
06-22-2020 10:55 AM
Let's break it down.
if you can list the requirements it would be easier to understand the sequence.