title: Create a dynamic list
body: <p>I have an excel sheet. I want to create a list which will be in 5 cells. For simplicity, lets call the items in the list as (item1, item2, item3, item4, item5). If I select "list1" from a cell-1, the item-contents in other list should become (item2, item3, item4, item5) and upon scrambled; the list should re-include the data into the list. </p>
  
  <p>I have tried the following: </p>
  
  <pre><code>Sub PopulatingArrayVariable()
  'PURPOSE: Dynamically Create Array Variable based on a Given Size
  
  Dim myArray() As Variant
  Dim DataRange As Range
  Dim cell As Range
  Dim x As Long
  
  'Determine the data you want stored
   Set DataRange = ActiveSheet.UsedRange
  
  'Resize Array prior to loading data
  ReDim myArray(DataRange.Cells.Count)
  
  'Loop through each cell in Range and store value in Array
  For Each cell In DataRange.Cells
    myArray(x) = cell.Value
    x = x + 1
  Next cell
  
  End Sub
  </code></pre>
  
  <p>Example: </p>
  
  <p>Suppose there are 3 cells A, B, C. All these cells will have this list ( Consider this as a list that we see in data-validation or a static array). So, our cells will have the values in the list like ( NY, NJ, LA ). Once we select an element (NY) from cell A, the remaining elements of list to be shown in cell B, C should be ( NJ, LA). If this NY is selected by any other cell then it should not show up in cell B, C.</p>