Creating data range formula for 1 fields across multiple sheets/tabs ?

So I have an excel with multiple tabs/sheets each representing a month, so i.e. August 2011, September 2011 etc.

On each sheet I have some data figures, and one important figure which is in Table column "D5" on every sheet.

What I am trying to do is create a new tab/sheet called summary, on and on this sheet have a column chart which represents this "D5" figure for each month.

How can I write the data range in excel for chart to get this figure on all sheets ?

List the sheet names in, say, column A of your summary sheet - you will need to enter them as text values (i.e. with an apostrophe at the beginning like 'May 2011) so they do not get converted into dates, and then in B1 you could have this formula:

=INDIRECT("'"&A1&"'!D5")

and then just copy the formula down as far as you need to.

Hope this helps.

Pete