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

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

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.

Regards

2. ## Re: Returning the cell reference with array formula (2000 SR-1)

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
Else
End If
End Function

Usage:

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).

3. ## Re: Returning the cell reference with array formula (2000 SR-1)

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 <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

4. ## Re: Returning the cell reference with array formula (2000 SR-1)

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.

Steve

5. ## Re: Returning the cell reference with array formula (2000 SR-1)

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

<pre>Function FindMatchingAddress(what As Variant, where As Range) As String
Dim rCell As Range
For Each rCell In where
If rCell.Value = what Then
Exit Function
End If
Next
End Function</pre>

6. ## Re: Returning the cell reference with array formula (2000 SR-1)

Steve,

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

7. ## Re: Returning the cell reference with array formula (2000 SR-1)

This does exactly what I want it to do.

Thank you very much

8. ## Re: Returning the cell reference with array formula (2000 SR-1)

This works too!

9. ## Re: Returning the cell reference with array formula (2000 SR-1)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•