title: Accessing named ranges with a particular scope using openpyxl
body: <p>How do I use <code>openpyxl</code> to access named ranges with a particular scope? I have an Excel workbook with several sheets, that have named ranges with the same name.</p>
  
  <p><img src="https://i.stack.imgur.com/9lzmf.png" alt="enter image description here"></p>
  
  <p>If I try to use the <code>get_named_range</code> function on the worksheet it doesn't necessarily return the correct range, and raises an exception. e.g.</p>
  
  <pre><code>&gt;&gt;&gt; from openpyxl import load_workbook                                                                
  &gt;&gt;&gt; wb = load_workbook(filename='database.xlsx')                                      
  &gt;&gt;&gt; wb['Residential Damages'].get_named_range('MCM')                                                     
  Traceback (most recent call last):                                                                       
    File "&lt;stdin&gt;", line 1, in &lt;module&gt;                                                                    
    File "c:\Python27\lib\site-packages\openpyxl\worksheet\worksheet.py", line 480, in get_named_range     
      raise NamedRangeException(msg)                                                                       
  openpyxl.exceptions.NamedRangeException: Range $B$3:$B$17 is not defined on worksheet Residential Damages
  </code></pre>
  
  <p>(What has happened in the error above, is that the range returned is for the "Non-Residential Damages" spreadsheet)</p>
  
  <p><a href="https://openpyxl.readthedocs.org/en/latest/api/openpyxl.worksheet.html?highlight=get_named_range#openpyxl.worksheet.worksheet.Worksheet.get_named_range" rel="nofollow noreferrer">https://openpyxl.readthedocs.org/en/latest/api/openpyxl.worksheet.html?highlight=get_named_range#openpyxl.worksheet.worksheet.Worksheet.get_named_range</a></p>
  
  <p>I've written a function that can return the correct range, but it's inefficient as it involves iterating through all of the ranges in the workbook. Is there a better way to do this?</p>
  
  <pre><code>def get_named_range(workbook, sheet_name, range_name):
      sheet_names = wb.get_sheet_names()
      named_ranges = wb.get_named_ranges()
      for named_range in named_ranges:
          scope_name = sheet_names[int(named_range.scope)]
          if scope_name == sheet_name:
              if range_name == named_range.name:
                  return named_range
      raise KeyError('Named range not found')
  </code></pre>