How to keep a value in the Excel validation list dropdown as one item if it has a comma in between.

Question

Hello,

We are making a c# application that would generate a XLS file of a certain format. On one of the columns we have added a validation list the values of which would be Dynamic(coming from the database). Sometimes there are values such as "Shoes,Apparel". Problem is if we put this as a formula in the validation list, they appear as separate items.

Now we're planning to add the values to a column in another sheet, define a name for the group of values, and use the name as formula in the validation list.

I get to have the values in the other sheet, but am confused which object should the name be. Should it be a NamedRange or a Excel.Name.

In either case how would you use it in the validation list. I've used Exel.Name object, but getting an exception while adding it to the validation list.

            string rngVal = "A1:A" + Convert.ToString(commodityCount - 1);
            Microsoft.Office.Interop.Excel.Range commodityRange = ws3.get_Range(rngVal, Missing.Value);

            Microsoft.Office.Interop.Excel.Name commList = ws3.Names.Add("CommodityList", commodityRange, true, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);


            commodityList = "= CommodityList";

            string endCell = "B" + rowCnt;
            deleteRange = ws.get_Range("B3:" + endCell, Missing.Value);
            deleteRange.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, commodityList, Missing.Value);
            deleteRange.Validation.InCellDropdown = true;

Any help would be highly appreciated Thank You