title: Insert variable string into formula
body: <p>Been working on this for a while, and can't seem to figure it out. I feel like I'm almost there, thanks to different posts in here. Here is what I am trying to do:</p>
  
  <p>I have a dependent list that I would like to change to it's first value based on the selection of the first list. I found a code here, that I modified to work for my sheet, but I had to change all the names in the first list to conform to the Define Name rules(ie. no spaces). This works, and I can leave it at that, but I would prefer to have the spaces in my list. Whenever I use the formulas I was using to change the name shown in the list, the VBA script no longer works. I feel that it is because it is not seeing a change in the target cell, although the value is changing, the formula in the cell is not. Is that an accurate statement? If so, what can I change to make it work the way I want it to? Right now, the dependent list is running off <code>=INDIRECT("$B$5)</code> but I would like it to run off <code>=INDIRECT('Store Data'!$A$23)</code> which contains a <code>VLOOKUP</code> formula.</p>
  
  <p>Here is the VBA script I am using now:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng(1) As Range, rng1 As Range
  Set rng(0) = Range("B5") 'your primary selection
  Set rng(1) = Range("G5") 'your secondary selection range
  Application.EnableEvents = False
  If Not Intersect(Target, rng(0)) Is Nothing Then 'if you have changed your primary selection
      For Each rng1 In rng(1) 'each cell in your secondary selection
          i = i + 1
          rng1 = Worksheets("SD Names").Range("" &amp; rng(0).Value2)(i, 1) 'gets changed to the nth value in the indirect reference of the primary selection ("TeamA" 's second row is "MemberA2" for example)
      Next
  End If
  Application.EnableEvents = True
  End Sub
  </code></pre>
  
  <p>Hope this is enough detail, thank you for any help!</p>