After the first test of the mega formula that will create content of our calendar, I spotted a problem:
Some formula tweaking is in order. I’m also going to trim the length of the Post Type entry to 2 characters, so only FB or TW will appear, And the Image (column E) will be trimmed to the last 3 characters, so only the file type will be visible.
I will use the current row number ROW() – 5 (the number of rows before the entries start) to help count down the entry list. I’ve also replaced the “stop” label with “” with empty quotes. Now when there is no matching entry, nothing will appear.
As I mentioned earlier, I’m trimming the Post Type entry to 2 characters using LEFT. I also decided to force those two characters to uppercase using UPPER. So the previous formula will be nested inside the LEFT and UPPER functions:
=UPPER(LEFT(IF(ROW()<5+(COUNTIFS(Content!$E:$E,”>=”&B$4,Content!$E:$E,”<“&B$4+1)),INDEX(Content!$C:$C,(MATCH(B$4,Content!$E:$E,0)+ROW()-5),1),””),2))
While it has been a long haul building this spreadsheet, now is not the time to stop. The next step will be adding the conditional formatting to this spreadsheet.
I offer Excel template design services and training. Feel free to send me an email – catharine@mytechgenie.ca
headline image via Kelly Sikkema