How reference multiple sheets in a formula

R

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....
 
I

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.
 
B

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.
 
Top