Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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