# Referencing a list

#### kyliehunter

##### New Member
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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Assuming your names are in column A, this will do the first part:

Sub macro()
), Unique:=True
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.

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

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

...

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.

Replies
1
Views
75
Replies
10
Views
530
Replies
2
Views
146
Replies
6
Views
511
Replies
1
Views
220

1,203,242
Messages
6,054,354
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back