So what do I use this for? Well as you might see in the bottom of my blog post I have a list of random blog posts from some of my other blogs to inspire you to go and read some of my other cool stuff.
To start with I have a sheet for each of my blogs. Here I have the name of the post and the direct link to my post:
The C column is just my way of telling if there are data in the table. We will come back to this later. I couldn't get the OData to work in Excel against my blogger RSS feed or Atom feed, so I cheated and used Google Spreadsheet to help me here, but here I also had a problem as it would only give my the last 20 posts. Well the older once isn't that relevant anyway. The function I used was:
=ImportFeed("http://www.photospots.dk/atom.xml?redirect=false&start-index=1&max-results=500", "items title",true,200)
After I have gotten the initial load I just update the list manually every time I write a blog post.
Well I wanted a random post. So I started by using the RAND() function which changes each time you edit a cell.
I have a simple CountIf() function to find how many blog posts that I have to choose between:
=COUNTIF(Photospots!$C2:$C201;TRUE) --> ($C2:$C201) is my C column with true/false from the above screenshoot)
Now I multiple my Rand() function with my Countif() and I get the random blog post that I am interested in:
=ROUNDUP(A1*A3;0) -->(A1 is Rand() and A3 is CountIf())
After this I use the Address() function to find the correct address:
=ADDRESS(A4+1;1;;;A2) -->(A4 is the value from the Roundup() where I add one as I don't want the first row (column headers), the constant 1 is just the first column that I am interested in and A2 is just the sheet name which is hardcodet.)
And finally I use the Indirect() function to get the value from the Address() function:
=INDIRECT(A8) -> ´(A8 is the Address() function)
To end this up I use the CONCATENATE function to create the HTML.
For each blog post I go into the sheet and copy the HTML code and insert in the HTML view of my blog post. This can be used for many other things than just this.