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


Using something like this could work for your HTTP,HTTPS,DNS cell:

=IF(COUNTIF(G3, "*"&H7&"*") > 0, I7)&", "&IF(COUNTIF(G3, "*"&H8&"*") > 0, I8)&", "&IF(COUNTIF(G3, "*"&H9&"*") > 0, I9)

In the example: G3 is your 1,2,3 cell and column H is your # reference and I is your protocol.

It's ugly as sin, but that seems to be the norm with Excel.

  • Ok this is just too complicated for 16 rows. I'll just write them manually. +1&answer – jamesbtate Mar 29 '10 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.