Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 3 down vote accepted

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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .