title: Dynamic Column lookup with different pages in excel
body: <p>I have a multi page spread sheet in excel that needs to read information dynamically from columns on other pages and have these values show up on a main page.</p>
  
  <p>This is the formula I'm using:</p>
  
  <pre><code>=IF(VLOOKUP($B:$B,'CP01'!$B:$BN,3,FALSE)="r","r",
          IF(VLOOKUP($B:$B,'CP01'!$B:$BN,3,FALSE)="a","a",""))
  </code></pre>
  
  <p><code>CP01</code> is a sheet in the excel file and instead of look at the specific sheet I want it to look at all of the sheets in the file.</p>
  
  <p>Is there a way to do this as an excel formula or with excel-VBA?</p>
  
  <p>Edit: I also tried <code>CP*</code> (* being a wildcard character) and it didn't work.</p>
  
  <p>Edit2: Is there a way to match the value where the 'CP' is placed with its a other columns value?</p>