title: Excel: Conditional Formatting with dynamic row reference
body: <p>I would like to use a certain conditional formatting for a larger table.</p>
  
  <p>The table is set up in a way that <strong>all headers and formulas repeat in fixed intervals</strong>.<br>
  - Row 2 contains the headers which get repeated every 31 rows, so again in row 33, 64, 95 etc.<br>
  - Rows 3:32 contain formulas which get repeated the same way, so again in rows 34:63, 65:94 etc.</p>
  
  <p>I would like to have just one conditional formatting that can be applied to each of these blocks and always references a block's header row (i.e. rows 2, 33, 64 etc).</p>
  
  <p><strong>For the first block I can achieve this with the following formula but this doesn't work if applied to the other blocks:</strong>  </p>
  
  <pre><code> =AND(($D2&lt;&gt;""),(E$2&lt;&gt;"x"))
  </code></pre>
  
  <p><strong>In order to adapt it for the other blocks, I tried to create a dynamic reference to the header rows instead of the fixed E$2 but this also doesn't work:</strong>  </p>
  
  <pre><code> =AND(($D2&lt;&gt;""),(INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),ROUNDDOWN(ROW()/30,0) +2))&lt;&gt;"x"))  
  </code></pre>
  
  <p>Can someone tell me what I am doing wrong here ? </p>
  
  <p>Many thanks in advance,<br>
  Mike</p>