Wow! You guys really know your stuff. It will take me some time to get this
one up and going, it is a bit over my head, but with a little work I will get
there and learn something new. Thank you so very much!

rmwarde

"Greg Wilson" wrote:

For some weird reason I couldn't get Application.Volatile to work earlier.
Now it works ??? Therefore, you don't need the named formula, just the UDF.
Insert "Application.Volatile" before "FusedRng = arr". Then in your cell
insert the formula, for example:
=LARGE(FusedRng("AM Kinder!C5:C26", "PM Kinder!C5:C19"),2)

Regards,
Greg


"Greg Wilson" wrote:

I was successful using a UDF and named formula combination. It wasn't very
elegant and no doubt someone else can do a much better job:

Step 1 - Paste to a standard module:-

Function FusedRng(r1 As String, r2 As String) As Variant
Dim arr() As Variant
Dim c As Range, rng1 As Range, rng2 As Range
Dim i As Integer, P1 As Integer, P2 As Integer
Dim ws1 As Worksheet, ws2 As Worksheet

P1 = InStr(r1, "!")
P2 = InStr(r2, "!")
Set ws1 = Sheets(Left(r1, P1 - 1))
Set ws2 = Sheets(Left(r2, P2 - 1))
Set rng1 = ws1.Range(Right(r1, Len(r1) - P1))
Set rng2 = ws2.Range(Right(r2, Len(r2) - P2))
ReDim arr(0 To rng1.Count + rng2.Count - 1)
i = 0
For Each c In rng1.Cells
arr(i) = c.Value
i = i + 1
Next
For Each c In rng2.Cells
arr(i) = c.Value
i = i + 1
Next
FusedRng = arr
End Function

Step 2 - Create named formula "XArray" with this RefersTo:
=FusedRng("AM Kinder!$C$5:$C$26", "PM Kinder!$C$5:$C$26")
Note absence of single apostrophes.

Step 3 - Have your cell formula reference XArray. Example:
=Large(XArray, 2)

Regards,
Greg

"rmwarde" wrote:

Let me try this again and explain better. I need to find the largest 3
numbers in a specific column of two different tabs. Can MAX find the top
three? or do I have to do LARGE? If I have to do LARGE how do I specify two
different tabs?