I have three columns in an Excel 2013 spreadsheet. One is an ID, one is a reference column, and one is a referenced by column. Some rows reference other rows, and some rows are referenced by other rows. The references could be a one-to-many relationship (many secondary rows reference one important row). Is there away to fill a cell with a list of cells that reference another cell?

| ID   |  REF | REF-BY |
| 001  |      |002,003 |
| 002  | 001  |        |
| 003  | 001  |        |

Thanks for the help

  • Have you tried Excel 2013's Inquire feature? k2e.com/tech-update/tips/… accountingweb.co.uk/article/excel-2013-inquire-add/535095 – CLockeWork Nov 19 '13 at 15:26
  • Just so we’re clear: are you asking how to generate Column C from Columns A and B? – Scott Nov 19 '13 at 16:44
  • yes, I want to put the contents of a2 and a3 into c1 – Trevor Newhook Nov 19 '13 at 19:50
  • Inquire solves the problem, but if possible, I'd like to do it through a formula, rather than having the user click on a button. I don't want them to need to enable Inquire unless necessary. – Trevor Newhook Nov 19 '13 at 19:51
  • can you put a macro in the workbook? – Jaycal Nov 20 '13 at 4:23

The way to go around this is to include a custom function in Excel to calculate the result for you. Include the following code in a new module in VBA. Press Alt+F11 to open the Visual Basic Editor, and then click Insert --> Module. Paste the following code ind the new module and close the Visual Basic Editor again.

Function MatchCriteria(ID As Range, REF As Range) As String
    Dim Cell As Range
    Dim Str As String
    For Each Cell In REF
        If Cell.Value = ID.Value And Cell.Row <> ID.Row Then
            If Str = vbNullString Then
                Str = Cells(Cell.Row, ID.Column).Value
                Str = Str & ", " & Cells(Cell.Row, ID.Column).Value
            End If
        End If
    Next Cell
    MatchCriteria = Str
End Function

You can now use this function as seen in the following picture.

enter image description here

The first input in the function defines the ID and the second input defines your "referenced by column". Note that in my version of excel (danish) the input delimiter is ";. If you are using an english version of Excel you should be using a comma, ,.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.