Returning the cell reference with array formula (2000 SR-1)

Topic

New Reply

I am using an array formula that I found on John Walkenbach’s site. The formula looks at a value in named cell and compares it to a named range and tells me if the value in the named cell is found within the named range.

I have called the named cell TheName and the named range SeatingPlan

The idea is to use the spreadsheet as a grid to allocate seats or spaces at a conference, trade shows or lecture and then use the formula to locate attendees on the spreadsheet.

The array formula I am using is {=IF(OR(TheName=SeatingPlan),”Attending”,”Not Attending”)

This tells me if the attendees have been allocated a place and I have also added conditional formatting to the named range so when an attendee’s name is entered into TheName cell their location will be highlighted on the spreadsheet. An instant visual aid to where they are in relation to everyone else.

But what I would like the formula to do is return the cell reference i.e. A3 or B6 rather than attending or not attending.

I have attached a spreadsheet.

Thank you in advance

Regards

Reply | Quote

Replies

Somebody else may come up with a solution just involving standard formulas. Here is a small custom function to do it (paste it into astandard module):

Function FindMatchingAddress(What As Variant, Where As Range) As String
Dim rng As Range
Set rng = Where.Find(What)
If rng Is Nothing Then
FindMatchingAddress = “#N/A”
Else
FindMatchingAddress = rng.Address(False, False)
End If
End Function

Usage:

=FindMatchingAddress(TheName,Mycontacts)

Note: the False, False as arguments to Address make the function return a relative address (A4); omitting them would result in an absolute address ($A$4).

Reply | Quote

Hello Hans

Pasted the code into a module in the workbook as instructed

and the value returned was #N/A.

I think I messed up somewhere

Reply | Quote

I don’t think you can use find in a worksheet function so it will always return #NA (this will only work as macro function. Try this code (or my previous array)

Steve

Function FindMatchingAddress(what As Variant, where As Range) As String
    Dim rCell As Range
    For Each rCell In where
        If rCell.Value = what Then
            FindMatchingAddress = rCell.Address(False, False)
            Exit Function
        End If
    Next
        FindMatchingAddress = "#N/A"
End Function

Reply | Quote

Steve,

Does the attached work in Excel 97? It does in Excel 2002…

Reply | Quote

No it does not work in XL97.

I had originally tried yours and I couldn’t see any reason why it would not work, so I called it from a SUB and it worked fine. Hence, my supposition that the FIND does not work when called from a worksheet function (at least, it seems, in XL97). I know there are others that don’t work (mostly involviing some kind of “selection” or “formatting” and this seemed like it might be one of the “selecting” type of issues.

My array “works” but could give a “miss” if multiple occurrences (it finds the min col and min row with that name), but the macro I proposed (while slower than yours) will work in XL97.

Steve

Reply | Quote

No it does not work in XL97.

I had originally tried yours and I couldn’t see any reason why it would not work, so I called it from a SUB and it worked fine. Hence, my supposition that the FIND does not work when called from a worksheet function (at least, it seems, in XL97). I know there are others that don’t work (mostly involviing some kind of “selection” or “formatting” and this seemed like it might be one of the “selecting” type of issues.

My array “works” but could give a “miss” if multiple occurrences (it finds the min col and min row with that name), but the macro I proposed (while slower than yours) will work in XL97.

Steve

Reply | Quote

Steve,

Does the attached work in Excel 97? It does in Excel 2002…

Reply | Quote

This works too!

Reply | Quote

This works too!

Reply | Quote

I don’t think you can use find in a worksheet function so it will always return #NA (this will only work as macro function. Try this code (or my previous array)

Steve

Function FindMatchingAddress(what As Variant, where As Range) As String
    Dim rCell As Range
    For Each rCell In where
        If rCell.Value = what Then
            FindMatchingAddress = rCell.Address(False, False)
            Exit Function
        End If
    Next
        FindMatchingAddress = "#N/A"
End Function

Reply | Quote

Hello Hans

Pasted the code into a module in the workbook as instructed

and the value returned was #N/A.

I think I messed up somewhere

Reply | Quote

Somebody else may come up with a solution just involving standard formulas. Here is a small custom function to do it (paste it into astandard module):

Function FindMatchingAddress(What As Variant, Where As Range) As String
Dim rng As Range
Set rng = Where.Find(What)
If rng Is Nothing Then
FindMatchingAddress = “#N/A”
Else
FindMatchingAddress = rng.Address(False, False)
End If
End Function

Usage:

=FindMatchingAddress(TheName,Mycontacts)

Note: the False, False as arguments to Address make the function return a relative address (A4); omitting them would result in an absolute address ($A$4).

Reply | Quote

This array formula (confirm with ctrl-shift-enter) will give you the address. If it occurs more than one time it might give you the wrong answer.

=ADDRESS(MIN(IF(Mycontacts=TheName,ROW(Mycontacts))),MIN(IF(Mycontacts=TheName,COLUMN(Mycontacts))),4)

Steve

Reply | Quote

This does exactly what I want it to do.

Thank you very much

Reply | Quote

This does exactly what I want it to do.

Thank you very much

Reply | Quote

This array formula (confirm with ctrl-shift-enter) will give you the address. If it occurs more than one time it might give you the wrong answer.

=ADDRESS(MIN(IF(Mycontacts=TheName,ROW(Mycontacts))),MIN(IF(Mycontacts=TheName,COLUMN(Mycontacts))),4)

Steve

Reply | Quote