A Spreadsheet is Creative (Part 3)

Now, I need to start to pull the post type, time of post and post content from the Content sheet. Starting with the post type (which resides in Column C), the following INDEX function in cell B6 will do the trick:
=INDEX(Content!$C$1:$C$1187,B5,1)
This index formula uses the value from B5 to find the entry in row 530. And yes, at this point that is only the first entry for the day.

Showing the result of the Index function
Index and If function results

Now, I’ll use an IF statement to build the formulas you see in column C6:
=IF(ROW()<6+C$5,"yes","stop")
At this stage in the process I usually use the IF statement to display a label. “Yes”, means there is matching data in the source sheet. “Stop” means there is no longer matching data. The test in column F shows the formula is working.
Now I’ve proven that the each formula works in turn, I can combine them into a larger “mega” formula. Don’t forget that when building these larger formulas you can; expand the formula bar and use Alt+Enter to force the formula to wrap for easier reading.
Like this:
=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)),1),"stop")

You’ll notice that I’ve also stopped referring to a specific range in column E/C and instead reference the entire column. It doesn’t make any difference to the result of this formula and makes it a bit simpler to read. Also the Row adjustment number changes from 6 to 5 as I play with the layout of the spreadsheet. Changing the layout includes reordering the data, so that the time of the post comes first and adding a column that shows the path to any images used in the post.

Here’s a shot of the results of that formula:

The formula is expanded to more columns
Now I’m capturing more columns of data

You’ve undoubtedly spotted that only the first entry is being captured and repeated. I’ll adjust the formula to correct that problem in my next post.


I offer Excel template design services and training. Feel free to send me an email.

unsplash-logoWilliam Iven

Leave a Reply

Your email address will not be published. Required fields are marked *