In all my years of spreadsheet design, there's only one reason I've ever used the INDIRECT function, but it's a very good reason. And that is to reference other sheets that have yet to be created. Here's an example of how this might work:
Imagine you have a spreadsheet where you track sales info for each month on a different sheet (Ex. Jan 2012, Feb 2012, Mar 2012, etc). What you would like is a summary sheet with total (such as cost, revenue, and profit) for each month all together in one place.
Without the INDIRECT function, your summary sheet would have to individually reference every month's sheet. And each time you added a month, you would have to usually reference the new month.
The INDIRECT function uses text as a reference to another cell. Imagine that you would like to reference cell B20 in the sheet Jan 2012, and you have Jan 2012 in cell A1 of the current sheet. Then you would input:
= INDIRECT ("'& & A1 &' '! $ B $ 20")
Since this is a little messy, I'll take you through it piece by piece. The INDIRECT function tells Excel that what it finds inside the parentheses, taken as text, is the cell you want it to reference. The "'" (That's a quote, followed by an apostrophe, followed by another quote) tells Excel that you want to use the apostrophe as text since it's in quotes. The ampersand (& symbol) tells Excel that there is more after the apostrophe. The A1 references cell A1 in this sheet, which says Jan 2012. So now, the function is using 'Jan 2012. But you have another ampersand, then "! $ B $ 20". That adds what is in quotes to the end, which makes the reference reference 'Jan 2012'! $ B $ 20. So this cell will contain whatever is contained in cell B20 of the sheet Jan 2012 (or whatever you have in A1).
The benefit is that if you were to copy this cell down, the A1 would change to A2, A3, A4, etc. So if you had Feb 2012 in A2 and Mar 2012 in A3, each cell would reference B20 on a different sheet. Now, as you continue to add more months, all you have to do is type the name of the sheet being referred into column A and drag the function down, saving time and effort.
Of course, this just scratches the surface of the possibilities. I personally like to use this in conjunction with the VLOOKUP function as a way to reference the range of cells to perform the lookup in, or in conjunction with the OFFSET function as a starting point from which I can move, but those are subjects beyond the scope of this article.