I have a chart of two series, Sheet1!Foo and Sheet2!Foo. They are named in the chart the same way - Foo. How can I include the sheet name in the series name?
A chart is based on the data contained in the Data Range from which it is created, and nothing else. Sheet name is not a part of the picture. In order for sheet name to appear within the chart, it has to appear in the header row/column within chart's range. This can be done automatically by a script or a custom function, but with two sheets, it's far easier to rename the headers manually.
