title: Google Spreadsheet dynamic conditional formatting with merged dropdown
body: <p><strong>How my sheet works</strong></p>
  
  <p>I'm making a spreadsheet to show how much parts I have. By using a dropdown, am I able to show that I created a product. With conditional formatting I am showing that having 0 items isn't an issue when the product is created. Created products with 0 items change from red to purple. Purple means it doesn't matter to have 0 items from this product. </p>
  
  <p><strong>My issue</strong></p>
  
  <p>My issue starts with my dropdown. If I merge cells, The value will go into the upperleft cell. This means other cells inside the merged cell are blank. This gives me a problem with conditional formatting.</p>
  
  <p><strong>My conditional formatting code example:</strong></p>
  
  <pre><code>=if($D2=0;$E2="Created")
  </code></pre>
  
  <p>I have to change this code for every cell because of the condition combined with a dropdown. Having more than 250 rows would be inhumanly hard to do by hand.</p>
  
  <p><strong>My questions</strong></p>
  
  <ol>
  <li>Are there ways to give all cells of a merged cell the value of the combined cell in an efficient way?</li>
  <li>Is there a better way to make my conditional formatting code applyable to merged cells?</li>
  </ol>
  
  <p><strong>This is my sheet</strong></p>
  
  <p><a href="https://docs.google.com/spreadsheets/d/11ZGH1gRXFzoAsayexdbslKmatQPIDZDYlRWZjupOaJw/edit?usp=sharing" rel="nofollow noreferrer">Product items collected sheet link</a> (Shows the problem and solution!)</p>
  
  <p><a href="https://i.stack.imgur.com/Il8rm.png" rel="nofollow noreferrer">Product items collected sheet image (Version 1)</a></p>
  
  <p><a href="https://i.stack.imgur.com/OAQDC.png" rel="nofollow noreferrer">Product items collected sheet image (Version 2)</a></p>