Referencing a list

[kyliehunter]

I want to create a list of names on a seperate worksheet and create a reference number linked to each name.

John Smith 1
Susan 2
Bill 3

Then on different worksheets in a single cell I want to reference these names.
So on one worksheet in a single cell I might want 1,3 (which would read as John Smith, Bill).

Thanks very much for your help

Kylie

 

[tactps]

Assuming your names are in column A, this will do the first part:

Sub macro()
Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1" _
), Unique:=True
Sheets.Add
Sheets("Sheet1").Select
Columns("D:D").Select
Selection.Cut
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A1").Select
Do Until ActiveCell.Text = ""
ActiveCell.Offset(0, 1).Value = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Then let's say you type "1,3" in cell B1. You could put this in B2:
=INDEX(Sheet5!A:A,VALUE(LEFT(B1,SEARCH(",",B1)-1)))&" , "&INDEX(Sheet5!A:A,VALUE(MID(B1,SEARCH(",",B1)+1,100)))

This assumes that there are only two references.

 

[kyliehunter]

Thanks very much.

I've got the first part working (the marcro) but I'm struggling a bit on the second part.

I can get it to work if the list of names and the formula are on the same sheet, but can't get it to work if the names with the unique number are on a seperate sheet.

Could you explain the formula a bit more for me?

I need to type 1,3 in box A1 then in A2 have the list of names appear.

Thanks so much for your help, I really appreciate it.

Kylie

 

[kyliehunter]

ok I've got it working!

How do I extend the formula so that I can put in more than 2 numbers.

Eg 1,3,4,6,8 etc

Thanks again

 

[kyliehunter]

...

 

[tactps]

How many do you need, and will it be different each time?

If so, I would recommend that you list the numbers in different cells and use the index formula on those.