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:
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 , "," , "J"+B1 , "," "J"+B1)
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.