Help needed with reference formula

[Scott]

I have a question and answer database that I am maintaining. Each question
has an ID number. Additionally, each answer for the question has other
relevant questions linked to that answer. What I want to do is to create a
column in this database that will tell me for each question ID, what other
questions it's linked to, so when it comes time to change a question or
answer, I know which questions to go back to for updates. I will have over
500 questions and answers, so a formula like "COUNTIF" probably won't work.

For example, here is a brief overview of the database.

A B C D E
1 500 504 501
2 501 503 504
3 502 504 500 501
4 503 502 500 504
5 504 500

Column A = Question ID #
Columns B, C, and D = Question ID's of questions that are related to the
Question ID in column A.

Now, what I want to do is in Column E, return the values or the Question
ID's where the original Question ID in Column A appears. So for instance,
for ID# 500 in A1, I want the end result to be "502, 503, 504" since question
500 is a relevant and linked question to questions 502, 503, and 504.

I could have close to 500 or more original questions, but the linked, or
child questions should be 12 or less for each original or parent question.

All of you guys are much more brilliant than I am. Any ideas on how to come
up with those end results? I'm looking for a formula or a series of
formulas.

Thanks for the help! It's much appreciated.

 

[Luke M]

You could try this UDF (right-click on sheet tab, view code. Goto Insert -
Module, paste this in. Close VBE)

'===========
Function FindRelation(Find_Me As Range, Search_area As Range, _
Optional Seperator As String = ", ") As String
If Find_Me.Count > 1 Or _
Search_area.Count < 1 Then
'Make sure adequate info is provided
FindRelation = "#VALUE!"
Exit Function
End If

For Each c In Search_area
If c.Value = Find_Me.Value Then
'Returns row header if found
FindRelation = FindRelation & Cells(c.Row, "A").Value & Seperator
End If
Next c
If Len(FindRelation) > 0 Then
FindRelation = Left(FindRelation, Len(FindRelation) - Len(Seperator))
End If
End Function
'================

Back in your workbook, your formula becomes:
=FindRelation(A1,$B$1:$D$500)

Note that you can specifiy a different seperator, if desired:
=FindRelation(A1,$B$1:$D$500,"; ")

 

[Scott]

Thanks for your reply. However, that didn't return any results. I'd also
like to avoid using codes if possible. But I didn't think about the
FindRelation formula. You might have hit on something there. Is there a way
for Excel to display something in a particular cell in a row where it finds
matching data? Of course, there would be more than one match. So does that
affect anything?

 

[Scott]

Luke,
I modified your formula a little bit and got it to work!

Thanks!

 

[Luke M]

Glad to hear, and thanks for the feedback!