merge cells

[ajl_ahmed]

I need a formula or function to merge specific cells dynamically depending on the value of another cell. In this attached file, if the cell (I9) contains "Suspended", then cells (H, G, F, E, D) in row 9 are merged dynamically.

[#2]

@ajl_ahmed a spreadsheet (in cell) formula can NOT affect the value or format of any other cell on the sheet, it will ONLY display the resulting value in the cell with the formula in it. To change other cells or formatting dynamically you would need a macro (VBA). That said, you can do quite a bit using cell formulas that would be equivalent or similar if you describe what you need a little more. For example what do you mean by "merge"? Do you mean the actual merge functionality where those cells are all merged into a single cell so you can't click them individually or do you mean you want all the text/values to be "merged" (concatenated) into a single cell. For example, you COULD hide columns D-H and insert 5 new columns next to them so the old column I is now column N so then in column I have a formula like =IF(N9="Suspended", CONCATENATE(H9:D9), H9) and then column J has formula like =IF($N9="Suspended", "", I9) and then copy right into columns K,L,M
If you prefer you could use TEXTJOIN to add a space or comma or something between cells. 

see example in the attached.

[#3]

@ajl_ahmed 

An alternative: In the attached I've just used Excel's Conditional Formatting tool to draw a heavy border around the "target cells." As @mtarler has pointed out, you can't use a formula to accomplish a literal merging of the cells--and even if you could, merged cells have their own down-side, depending on what you're doing with the spreadsheet. His questions to you are all worth considering--what exactly do you have in mind when you say you want them "merged"?

My suggestion accomplishes a "visual merge"--and in this case I just use a border; you could add color as well--and it does depend on the word "Suspended" being in cell I9, as requested. So it's not a literal merge, but it looks like it.......

What really will work for you depends a LOT on what you actually have in mind.