Hi Paul,

Thanks for your response. I am looking for a one-time solution which will automatically keep updating as changes are made to the main sheet.

I am using the main sheet and a Dashboard sheet (which will be used to summarize data for the Dashboard).

In the main sheet, there are various regions in a column (which can be repeated multiple times).

In the Dashboard sheet, I have used the below formula to summarise all regions in one cell separated by commas:

=JOIN(DISTINCT(COLLECT({ColumbiaScope_ClassIII_BU}, {ColumbiaScope_ClassIII_BU}, @cell <> "")), ", ")

Now, my aim is to populate these regions in a column automatically. I cannot use LEFT or RIGHT functions since they only really work for a specific number of values. Would you have any recommendations?

Appreciate your help!

Yes ofcourse.. For example, this is the main sheet with all the data:

And this is the Dashboard sheet which will feed into the Dashboard. Cell B2 has the formula: =JOIN(DISTINCT(COLLECT({Main Sheet_BU}, {Main Sheet_BU}, @cell <> "")), ", ")

I am looking for cell B2 values to be populated in Column A under BU Title.

Note: This field is not limited to only 4 values.. it can vary from none to 40.

Also to make sure, if any new rows are added in the Main Sheet, we want the cell B2 in Dashboard sheet and the column A under BU Title to be populated automatically.