General discussion

Locked

Chart data ref (at diff worksheets)

By rchong ·
I have many similar worksheets (similar data table formats) within a workbook. I also have created only one chart to show the data (multiple series) for ONE particular worksheet.

Q1. Are there any tricks that requires a simple change of one cell content (with the cell storing the worksheet name), and then the chart would plot the data of the corresponding worksheet accordingly?

Thanks
Richard

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Chart data ref (at diff worksheets)

by joescalia In reply to Chart data ref (at diff w ...

1)Right click on your chart and select source data
2)Select the Series Tab
3)Go to the Values box and edit them accorrdinging.
4) Select ok button
Your title and other text will not be effected.
For example if you had:
series 1 with a vaule of =sheet1!$a$1:$a10
Series 2 with a vaue of = sheet1!$b$1:$b$10
and you want to add a new vaule for series 1 you would edit the vaule to read =sheet1!$a$1:$A$15 and for series 2 you would do the same.
If would what to add a new series call it 3
then select the add button and then edit the value box to read as =sheet1!$c$1:$C$15.
When you enter your new data in the range of c1:c15 it will be plotted automatically.
Good Luck...

Collapse -

Chart data ref (at diff worksheets)

by rchong In reply to Chart data ref (at diff w ...

Poster rated this answer

Collapse -

Chart data ref (at diff worksheets)

by KayCS In reply to Chart data ref (at diff w ...

The easiest way to do this, in my experience, is to create a dummy worksheet containing a reference to the data to be graphed. Because your worksheets are all similar this becomes easy.

Copy one of your existing worksheets - at least the data to be graphed.

What we will do is insert an additional row at the top of the sheet to contain the name of the worksheet to be graphed. Let us assume you have done this and have typed Sheet3 as the name of the worksheet to be graphed.

For each value to be graphed, you will need to write a small formula that looks at the content of cell - this is rather an old fashioned idea, but it works well. Let us assume that you want to pick up B3 on the appropriate worksheet, the formula will be =INDIRECT(A1&"!B3")

This formula takes the contents of A1 (in this case "Sheet3"), concatenates it with !B3 to make "Sheet3!B3" then takes the contents of that reference as the value of the formula. I can send you an example if you are interested.

Now, create the graph you need based on the data sheet.

When you wish to graph another worksheet, simply change cell A1 to read the name of that worksheet and the graph will automatically update.

Good luck! Hope this works as well for you as it doesfor me.

Collapse -

Chart data ref (at diff worksheets)

by rchong In reply to Chart data ref (at diff w ...

Poster rated this answer

Collapse -

Chart data ref (at diff worksheets)

by rchong In reply to Chart data ref (at diff w ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums