Formula to write multiple values to a cell based on value of other cells

Hi,

I've got a bit of a tricky problem I'm trying to work out. To be honest, I'm not even sure if it can be accomplished with a formula, but I'm sure someone in here can tell me.

I've got a spreadsheet with 2 columns, named Col1 and Col2. Some of the cells in these columns contain values, and others don't. I want to write a formula which will look across the row and see whether Col1 and/or Col2 on that row are empty or not. If one or both of the cells in that row contains data, I want to write the column name that contains data into a 3rd cell. I hope that makes sense.

I've tried using the following formula, which works fine, but it breaks as soon as I try to extend it to the other column.

Please Login or Register  to view this content.

Any ideas?

Thanks!

Thanks very much for the reply FDibbins

The 2nd scenario is what I wanted, I think. Would there be a way to avoid specifying both column names if both columns contain data, and just run through the columns individually and append the values into the "target" cell?

The reason I ask is because if I were to expand the table to have 3 columns, the formula would have to change to manually specify options for Col1+Col2+Col3, Col1+Col3, Col1+Col2, etc.

Thanks FDibbins and dredwolf for your replies. Neither of them quite worked 100%, but someone else gave me a formula which did work, so I'll post it here in case someone is looking for something similar.

Please Login or Register  to view this content.