Excel concatenate strings from cells listed in third cell
- by Puddingfox
I have an excel 2007 workbook that has five columns:
A. A list of machines
B. A list of service numbers for each machine
C. A list of service names for each machine
...(nothing here)
I. A list of Service Numbers
J. A list of Service Names
Each machine listed in column A has one or more services running on it from the list in column J. I would like to be able to add services to a machine (i.e. updating the cell in Column C) by simply adding another comma-separated number to Column B. For Example, The first row would look like this assuming Machine1 has the first three services:
| A | B | C
| Machine1 | 1,2,3 | HTTP,HTTPS,DNS
Right now I have to manually update the formula in column c for each change I make. The current formula is:
=CONCATENATE(J1,",",J2,",",J3)
I would like to use something like this (please forgive my syntax; I'm a coder and I'm treating cell B1 as if it is an indexed array):
=CONCATENATE(CELL("J"+B1[0] , "," , "J"+B1[1] , "," "J"+B1[2])
Although having variable numbers of services makes this even more difficult.
Is there any way of doing this. For reference, this is columns I and J:
| I | J
| 1 |HTTP
| 2 |HTTPS
| 3 |DNS
.....
| 16 |Service16
I don't know very much about Excel so any help is greatly appreciated.
