=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

=MAX(Sheet1!C5:C26, Sheet2!C5:C26)

Regards,
Greg

"rmwarde" wrote:

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

=MAX('AM Kinder'!C5:C26,'PM Kinder'!C5:C19)

--


Gary


"Greg Wilson" wrote in message
...
=MAX(Sheet1!C5:C26, Sheet2!C5:C26)

Regards,
Greg

"rmwarde" wrote:

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

I really want to use the LARGE formula because I need to find the 1,2,and 3
top numbers. Thanks

"Greg Wilson" wrote:

=MAX(Sheet1!C5:C26, Sheet2!C5:C26)

Regards,
Greg

"rmwarde" wrote:

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

Sheet names should match yours of course:

=MAX('AM Kinder'!C5:C26, 'PM Kinder'!C5:C26)

Greg

"rmwarde" wrote:

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

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?

do some calcs. on sheet3, c3:c5

=LARGE('AM kinder'!C5:C26,1)
=LARGE('AM kinder'!C5:C26,2)
=LARGE('AM kinder'!C5:C26,3)

d3:d5
=LARGE('PM Kinder'!C5:C19,1)
=LARGE('PM Kinder'!C5:C19,2)
=LARGE('PM Kinder'!C5:C19,3)

e3:e5
=LARGE((sheet3!C3:d5),1)
=LARGE((sheet3!C3:d5),2)
=LARGE((sheet3!C3:d5),3)


--


Gary


"rmwarde" wrote in message
...
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?

That's what my final resort was going to be. I was hoping for an easy way
out of it. Thank you so much and hope you have a great rest of the day.

"Gary Keramidas" wrote:

do some calcs. on sheet3, c3:c5

=LARGE('AM kinder'!C5:C26,1)
=LARGE('AM kinder'!C5:C26,2)
=LARGE('AM kinder'!C5:C26,3)

d3:d5
=LARGE('PM Kinder'!C5:C19,1)
=LARGE('PM Kinder'!C5:C19,2)
=LARGE('PM Kinder'!C5:C19,3)

e3:e5
=LARGE((sheet3!C3:d5),1)
=LARGE((sheet3!C3:d5),2)
=LARGE((sheet3!C3:d5),3)


--


Gary


"rmwarde" wrote in message
...
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?

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?

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?

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?

If you want to just use a formula:

=LARGE(CHOOSE({1,2},AM Kinder!C5:C26,PM Kinder!C5:C19,1)

Entered with Ctrl+Shift+Enter rather than just enter will do what you want.

--
Regards,
Tom Ogilvy



"rmwarde" wrote in message
...
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?