title: Using range names as cell source
body: <p>I've been trying to work on this thing in OpenOffice Calc, where I have two drop lists in a spreadsheet, in which data is coming from other columns. I've attached a snapshot for better understanding.</p>
  
  <p><a href="https://i.stack.imgur.com/teW8b.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/teW8b.png" alt="Example"></a></p>
  
  <p>Cells A1-A3 are represented by named-range <code>countries</code><br>
  Cells B2-B3 are represented by name-range <code>country1</code>.</p>
  
  <p>Named-ranges <code>country2</code> and <code>country3</code> represent B5-B6 and B8-B9 respectively.</p>
  
  <p>Cell A11 is a drop down list with range 'countries' as source so that it contains values : INDIA, ENGLAND ,AUSTRALIA.</p>
  
  <p>Cell B11 is another drop down list whose values are to be determined by the value chosen in A11 i.e. B11 is dependant on A11. As an example, if I choose 'England', then values in B11 must contain IJ, KL. In other words, a named-range has to be set as source for B11 depending on value from A11.</p>
  
  <p>Now the problem is, if I directly set the source of B11 with some named-range, say country1, it's successfully showing the values. I've shown the same here:</p>
  
  <p><a href="https://i.stack.imgur.com/SMjco.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SMjco.png" alt="successfully showing the values"></a></p>
  
  <p>but when I'm retrieving the same range name using formula, it's not showing the range. Rather it just sets the name of the range as value, as shown here </p>
  
  <p><a href="https://i.stack.imgur.com/Jp86w.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Jp86w.png" alt="not showing the range"></a></p>
  
  <p>The formula I've used for obtaining a range for source is:</p>
  
  <pre><code>CONCATENATE("country" &amp; MATCH(A11;countries;0))
  </code></pre>
  
  <p>Can someone explain why is it happening?</p>