Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    I have attached a spreadsheet.

    Thank you in advance

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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.

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

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
    FindMatchingAddress = rCell.Address(False, False)
    Exit Function
    End If
    Next
    FindMatchingAddress = "#N/A"
    End Function</pre>


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    This works too!

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •