Graphs from multiple worksheets

How can I create a line graph showing the value of a particular cell across a range of worksheets? So for example, I have a sheet every week called "13 Jun" or whatever the first day of the week happens to be, and I add a new sheet every week. And in cell F8 is the value that I want to appear in the graph. I know how to add these values and display the total on another worksheet, but I haven't worked out how to use them in a graph.

Hi, I'm new by the way. Any help would be appreciated.

May I see a sample of your workbook with the expected results.

Right, I've attached a screenshot of my spreadsheet (obviously for the purposes of this, none of the figures are genuine - it's not my company). So you'll see that there is a worksheet for each week (named by the date of the first day of that week), and all of these are identical in layout to the one you can see.

Basically, I'd like to pick any figure on the sheet (variance in the Restaurant, for example in cell B25) and use B25 from all of the worksheets to create a line or bar graph charting this data. So for example, if I wanted to add them all on a seperate sheet, I would use the formula "=SUM(Sheet1:Sheet2!B25)" and that would select all of the B25's between Sheet1 and Sheet2 (plus any I might add later). So what can I type into the "Series Values" when selecting my data for a graph, that will allow me to pick B25 from each worksheet in a similar way? Or is there another solution?

Okay, I thought I'd attached it, but apparently it was a bit big.

Have you try inputing this in your series box:

=Sheet2!$B$25+Sheet3!$B$25+Sheet4!$B$25


Hope it helps!

Nah that didn't work. I said there was an error in the formula. I also tried it with apostrophies either side of the Sheet name, but the same problem. But this won't solve the problem anyway, because every time I add a new sheet, I'd have to update the selected data on my graph, whereas if I can point it to all sheets between two, I can simply add new sheets as I go and the graph with update.

Another alternative then, would be to consolidate this data into a column on a seperate worksheet and run the graph from that. Does anyone know how to select all of the B25's in the same way, and have them displayed as a column on a seperate worksheet? Without me having to update it with each new sheet? So for example, a seperate worksheet would contain a column made up of the values in B25 on all of the worksheets between Sheet1 and Sheet 2, for example. And any new ones are simply added in the order the sheet appears?

I'm having the same problem...
What you can do is make another sheet (like you said) and enter the formula:

=SUM(

Then, go to the sheet you want the sum's to add up on (the formula should stay in the formula bar on the top) and click then shift click the cells you want added together.
Example:
=SUM(July!H3:H17)

This will give you a sum of all the cells.

To add it to the chart in a separate Text box, select the text box then enter in the formula address:

=<sheet name>!$A$1 (without the < and the > )

Example:
=Julynumbers!$G$22

I am hoping someone will have a formula that will add the sum of cells that can be inserted in a chart text box, which would make life much easier!

Originally Posted by I'm With Stupid

? So for example, a seperate worksheet would contain a column made up of the values in B25 on all of the worksheets between Sheet1 and Sheet 2, for example. And any new ones are simply added in the order the sheet appears?

Try inputing this formula and copy down.

=INDIRECT("'sheet"&ROW()&"'!B25")


Hope it helps!