How reference multiple sheets in a formula

[Rick]

I trying to make a chart with data that that s on multiple sheets How do I
select the data? I searched for an answer for this for 2 hours now....

 

[John Bundy]

99% sure you can't do it without code.

 

[Bob I]

make the chart with one, then simply add series to put in data from
other sheets

 

[Rick]

I want the data to be all in the same series.....

 

[iliace]

You can't, or at least it doesn't look like you can to me. I just
tried this. Wrote a UDF to return a single range object, then used it
in a defined name and tried to put the defined name into the Series
formula of a line... Excel says it's invalid.

Public Function RangeUnion(ParamArray rngs() As Variant) As
Excel.Range
Dim l As Long
Dim retVal As Excel.Range

For l = LBound(rngs) To UBound(rngs)
If retVal Is Nothing Then
Set retVal = rngs(l)
Else
Set retVal = Union(retVal, rngs(l))
End If
Next l

Set RangeUnion = retVal
End Function

You'll probably need to to copy your data into a single worksheet, in
a single column (or row) then make the chart.

 

[Bob I]

Then pick a "master sheet" to provide the "cells" that the series will
point to, and in those cells, reference the scattered data.