I am managing ad contracts and needing to include the year of their creation date plus the contract number in the first column. I use a formula that pulls data allowing me to have at-a-glance info for tracking our ads. Example:
the column is called CAMPAIGNS with the following formula:
CONCATENATE({Contract #}, ": “,Account,” - ",{Ad Size})
This results in a field that might read: “21 004-BQEA: Bruce’s Quick Eats - 1/8 page”
When blazing through production, I can see the year the contract started, that it was the fourth contract of the year, the account code and full name of the client, and finally the ad size they ordered.
We also use the 21 004 to populate part the invoice numbers for billing in each ad cycle. (Contracts generally cover multiple ad cycles). This also informs our file-naming for ad image files, so we can find accompanying assets quickly and easily.
I intially built contracts for each year in their own tables, because I needed to reset the contract number to zero every Jan 1, and I was unable to do so without changing the invoice numbers of our entire database every time I had to reset to zero.
I am wanting to merge my contracts into a single table with separate views for each year. This will make it MUCH easier for me to create a view of active contracts, as some contracts span multiple years. I have no clear idea of how best to do this and what formula(s) to use. Create new recount columns every year and update the formula to include an IF/THEN something or other that pull data from the current year?
I am not well versed in formulas. I’m a hack, so any guidance is appreciated. Thanks in advance.
