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.
Random posts:
