Appending string value in cell with new unique selection from its drop-down list
Hi, I'm very new to VBA programming and I'm not yet able to put together a macro I need. I'm not quite sure even where to get started. I'd like to create VBA Macro that allows a blank cell to be set a value via its drop-down list then take each subsequent value selected via the same drop-down lists and append new string values only to the current the string of the same cell, and separating each new entry by ",". Essentially the drop-down list allows pre-determined values to be conveniently selected, however in some instances multiple of those values from the drop-down list applies and want them also displayed. Ideally would also be great if any text values typed in the cell that are not already available in the drop-down list, to be added to the drop-down list. Any help with this would be much appreciated. Thanks in advance.
possibly use indirect
Hi Logical Choice,
Not 100% sure I get what you're asking for but maybe using the indirect function is the answer you're looking for? I'll use continents & countries in this example:
on sheet 2 you have in A1 = USA, A2 = Canada, A3 = Mexico, B1 = UK, B2 = France, B3 = Germany, C1 = China, C2 = Japan C3 = India. Highlight A1:A3 and rename these cells NorthAmerica, B1:B3 highlight and name Europe, C1:C3 highlight and name Asia (NB name box for quick renaming is just above column A in excel).
Now if in Sheet 1 create a list in cell A1 with NorthAmerica,Europe,Asia as your 3 options (Data - Data Validation - change allow = List and put NorthAmerica,Europe,Asia as your Source data). in cell B1
create another list, but in the source data enter =indirect(A1)
Now the drop down in B1 will only show the countries that are in the list for the relevant continent selected in cell A1
Hope that helps
Regards
Mark