Well Gary"s Student, that new code of yours is simply
OUTSTANDING!!!!!.....When I grow up
I want to be able to write code "jus like you"............seriously, I do
really
appreciate your kindness, and considerable talent put forth to solve this
problem for me. Thank you most kindly.

This new version runs equally well on my XL2k and XL97SR1 I have here at
home. Whereas
the first one did not run on the XL97SR2 I had at work, nor will it run on
XL97SR1 here at home. I dunno what the difference is, but this new one
really does good on both versions. Even tho I also have XP at work, I
really need it to be '97 compatible, because most of my users only have 97,
and it's like pulling teeth to get the MIS dept to upgrade them.

Incidently, this also works.....
=titles(INDIRECT(J1),MyLookup)
with "S100:X105" in J1 and P88 named "MyLookup", but I have been unable to
get a RangeName to specify the matrix in the formula..........

But that's "SmallStuff", the main thing works and for that I thank you again
and again.

Vaya con Dios,
Chuck, CABGx3




"Gary''s Student" wrote in message
...
New code:

Function titles(r As Range, vv As Range) As String
Dim rr As Range, s1, s2 As String, gotit As Boolean
titles = ""
gotit = False
v = vv.Value ' NEW LINE

For Each rr In r
If rr.Value = v Then
gotit = True
Exit For
End If
Next
If gotit = False Then Exit Function

s1 = Cells(r.Row, rr.Column)
s2 = Cells(rr.Row, r.Column)
titles = s1 & Chr(10) & s2
End Function

1. changed header from integer to range
2. added a line to get value from range
3. tested it on:

xxx 2001 2002 2003 2004 2005
dogs 1 2 3 4 5
cats 10 9 8 7 6
pigs 11 12 13 14 15
fish 20 19 carp 17 16
birds 21 22 23 24 25

in S100 thru X105. This version works on numbers, text, and dates.

There is a slight (better) difference in usage. For example in P88 enter
17
or carp.

Then use as =titles(S100:X105,P88) so we refer to a cell rather than
putting the value in the formula.


I no longer have access to a machine with Excel97 on it. I looked at the
code and its plain vanilla. Should work on 97; just can't test it.


Have a pleasant weekend.
--
Gary's Student


"CLR" wrote:

Thanks much Gary"s Student.........no great rush. I am wanting to use
this
for an upcoming project.....plenty of time. Also, just discovered it
don't
seem to work in XL97......could something be changed to
allow?................I really appreciate your help.

Thanks again,
Vaya con Dios,
Chuck, CABGx3




"Gary''s Student" wrote:

Hi CLR

I'll look at TEXT values later today. Right now the undergrads are
swarming
all over our help center. I'll be tied up until after 6:00PM (east
coast).
--
Gary's Student


"CLR" wrote:

Hi Gary"s Student, thanks for the reply. I had a little trouble
with it at
first, getting some #VALUE! and #NAME errors.....but then I got the
knack and
it seems to be ok now. I really prefer this method over the other
much
longer formula suggestions, except that they both work on TEXT and I
can't
seem to make this one do it. It seems to fail when trying to look
up a TEXT
value, as well as any number in a row that has any TEXT in it and
any row
thereafter. I know that was not a requirement in the original
problem, but
could your solution be easily modified to work with both TEXT and
numbers as
well? If so, I would be much appreciative.

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Gary''s Student" wrote:

From S100 thru X105:

xxx 2001 2002 2003 2004 2005
dogs 1 2 3 4 5
cats 10 9 8 7 6
pigs 11 12 13 14 15
fish 20 19 18 17 16
birds 21 22 23 24 25


a 5x5 with titles

Function titles(r As Range, v As Integer) As String
Dim rr As Range, s1, s2 As String, gotit As Boolean
titles = ""
gotit = False

For Each rr In r
If rr.Value = v Then
gotit = True
Exit For
End If
Next
If gotit = False Then Exit Function

s1 = Cells(r.Row, rr.Column)
s2 = Cells(rr.Row, r.Column)
titles = s1 & Chr(10) & s2
End Function


=titles(S100:X105,12) will display:


"2002
pigs"

--
Gary's Student


"CLR" wrote:

Hi All......
I have a small 5x5 matrix on a worksheet. Normally one
supplies the Row
and Column Titles of a matrix to return the crossover value. I
want to do it
in reverse. I want to supply the crossover value and in return
get the Row
and Column Titles from the matrix, (not the Excel cell address).

TIA for any assistance,
Vaya con Dios,
Chuck, CABGx3