Search function using commas
 

I have created a list of names on a seperate worksheet and each has a unique
number

1 John Smith
2 Susan
3 Bill
etc

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

Say I type "1,3" in cell B1. I have this in B2:
=INDEX(Sheet5!A:A,VALUE(LEFT(B1,SEARCH(",",B1)-1)))&" ,
"&INDEX(Sheet5!A:A,VALUE(MID(B1,SEARCH(",",B1)+1,1 00)))

This works fine. How do I then expand this function so I can reference more
than two names EG in Cell B1 put 1,3,4,6,7 etc

Thanks for your help
Kylie

I think this is going to be one unwieldy formula.

An alternative maybe to use:
tools|autocorrect options.

then when you type your numeric value, it'll be replaced as you type.

Another option would be to use a series of replaces.

Edit|Replace
what: 1
with: firstnameinlist
replace all

And string them together.

You could have a macro that did this based on that table on the other sheet.



Kylie wrote:

I have created a list of names on a seperate worksheet and each has a unique
number

1 John Smith
2 Susan
3 Bill
etc

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

Say I type "1,3" in cell B1. I have this in B2:
=INDEX(Sheet5!A:A,VALUE(LEFT(B1,SEARCH(",",B1)-1)))&" ,
"&INDEX(Sheet5!A:A,VALUE(MID(B1,SEARCH(",",B1)+1,1 00)))

This works fine. How do I then expand this function so I can reference more
than two names EG in Cell B1 put 1,3,4,6,7 etc

Thanks for your help
Kylie

--

Dave Peterson