Locate values in range

[Matt]

Not sure if this can be done with cell formulas...

I want to create a function so that given a range that is 20 x 30, it
would return the row and column coordinated for each cell containing a
particular value (the letter "N").

Any idea if this doable with functions? or would this require VBA?

 

[Gary''s Student]

Try this small UDF:

Function nFinder(r As Range) As String
nFinder = ""
For Each rr In r
If rr.Value = "N" Then
If nFinder = "" Then
nFinder = rr.Address
Else
nFinder = nFinder & ", " & rr.Address
End If
End If
Next
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function. For example:
=nFinder(A1:Z100)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx