CODEWITHSUNDEEP
excelexcel-2010
Sunny
Sunny

Reputation: 7604

Excel Chart: How to add data points in one series from multiple sheets

I have an excel document with multiple sheets. Each sheet contains data of separate test executions. Now I would like to create to see the overall trends for all the executions. This requires that I use data from multiple sheets in one series. An example to make this clear is that my y-axis would be constant but my y-axis of the chart would contain the following data: 

1st entry: Sheet1 Cell B4 
2nd entry: Sheet2 Cell B4
3rd entry: Sheet3 Cell B4
4th entry: Sheet4 Cell B4
.....and so on.

Now, as i keep doing more executions, I would like to add data to the existing chart.

COuld someone please help me with the following two points in excel: 1. How to add data to a single series from multiple sheets, as mentioned above by example? 2. How do I keep adding data to the chart after each execution?

Thanks, Sunny

Upvotes: 1

Views: 10868

Answers (2)

brettdj
brettdj

Reputation: 55672

What do you mean by each execution - adding new sheets, or graphing different cells (ie B5 after B4 etc?)

For the graph the easiest way would be to build a simple summary range on one sheet that pulls in B4 from Sheet1 as the first value, B4 from Sheet2 as the second value etc

Two quick ways to build this list are to:

  • Use the Sheets ..... Put together rows or columns from several sheets.." functionality from ASAP Utilities
  • Build a sheet index using INDIRECT in conjunction with a formula that drops out repeatable sheet names
    1. ="Sheet"&ROW()-1 in A2 will give "Sheet1", copied to A3 will give "Sheet2" etc.
    2. Then in B2 enter =INDIRECT("'"&A2 &"'!" &$B$1) to pull B4 from Sheet1 etc

The second method can quickly be updated to pull a different cell by changing the B4 value in cell A1 to another cell (say B5)

enter image description here

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6053

INDIRECT(string) will return the value of the range identified by 'string'. The formula should actually be

=INDIRECT("'"&A2 &"'!"&$B$1) so string is "'Sheet1'!B4", since A2 holds Sheet1 and $B$1 holds B4

This indirect then provides the value in cell B4 of Sheet1.

Upvotes: 0

Related Questions