How To Use Structured Table-Referencing in Excel Data-Validation?
<p>I'm attempting to create Data Validation for a column in an Excel table. The validation needs to reference another column in the table. </p>

<p>There are 2 columns, "f1" and "f2". Each column can contain either "Y" or "N". </p>

<p>Using cell-addressing works fine:</p>

<p><a href="https://i.stack.imgur.com/7vxXt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7vxXt.png" alt="enter image description here"></a></p>

<p>My question is: how to make a validation-formula with structured table-addressing? </p>

<p>Excellent overview of structured-referencing: <a href="http://peltiertech.com/structured-referencing-excel-tables/" rel="nofollow noreferrer">http://peltiertech.com/structured-referencing-excel-tables/</a>  </p>

<p>The desired validation on column "f2" is:</p>

<pre><code>=Table1[f1]&amp;Table1[f2]&lt;&gt;"YN"
</code></pre>

<p>This pic shows the table and validation formula above entered next to the table, showing expected validation results:</p>

<p><a href="https://i.stack.imgur.com/zibyC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zibyC.png" alt="Pic1"></a></p>

<p>I added the above formula into Data Validation --> Custom, for cells in "f2". That fails-- it rejects valid entries (ie entries for which that formula evaluates to True). </p>

<p>Then, I tried creating a defined name using that formula, based on this post, and used that name as my custom validation. That also fails. 
<a href="https://superuser.com/a/480660">https://superuser.com/a/480660</a></p>

<p>Then I tried using INDIRECT in my defined name, based on this post:
<a href="http://www.get-digital-help.com/2012/10/15/how-to-use-a-table-name-in-data-validation-lists-and-conditional-formatting-formulas/" rel="nofollow noreferrer">http://www.get-digital-help.com/2012/10/15/how-to-use-a-table-name-in-data-validation-lists-and-conditional-formatting-formulas/</a></p>

<p><a href="https://i.stack.imgur.com/55UPU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/55UPU.png" alt="enter image description here"></a></p>

<p><a href="https://i.stack.imgur.com/aP6bC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aP6bC.png" alt="enter image description here"></a></p>

<p>But valid entries still getting rejected. </p>

<p>How to do this?</p>

<p>I think the reason structured-referencing fails in a validation formula is because TableName[FieldName] evaluates to the entire column, not just the current row. Therefor, i'm exploring ways to limit the validation-formula to just the current row. </p>

<p>I thought of these methods so far:</p>

<p>I think that in structured-referencing, the current row is identified with @. </p>

<p>So, the formula would be:</p>

<pre><code>Table1[@f1] &amp; Table1[@f2]&lt;&gt;"YN"
</code></pre>

<p>or</p>

<pre><code>Table1[[@[f1]] &amp; Table1[@[f2]]&lt;&gt;"YN"
</code></pre>

<p>That actually works on sheet, but still does not work as a validation-formula. </p>

<p>My next try was to use INDEX to pull just the current row. The formula is:</p>

<pre><code>INDEX(Table1[f1] &amp; Table1[f2],1,0)
</code></pre>

<p>Again, that works on-sheet, but not as a validation-formula. I think this is a promising direction, though!</p>