Using using a multi-column table for a data validation rule

Hi Coupland,

Excel just added some new Dynamic Array Functions and this is a perfect problem to show them off. See the attached and read the link:

https://www.excelcampus.com/function...-spill-ranges/

Dynamic Array Unique Sort Validation.xlsx

I hope you have the 365 subscription and can update your version. I'm using 365 Home and have updated mine...

I think you’ll find they are not in Excel 2016, though, only Office 365.

Thanks, but this doesn't really accomplish what I'm looking for.

Let me try explaining it in another way. Let's imagine I have a large table named Orders. One of the columns in that table is named "ProductOrdered". I have another table named Products that contains a bunch of columns, including ProductName.

When someone enters a new row in Orders and they fill out the column ProductOrdered, I want a validation rule to makes sure this is a valid ProductName in the table Products.

All I can get to work as a validation rule is "=Data!$B$3:$B$26". But I don't want to use that because that range doesn't adjust dynamically as the Products table grows. I'm looking for a validation rule that references the column ProductName in table Products.

I thought my validation rule would be something simple like:

=Products[ProductName]

Or something like that, but it generates an error. Can I specify a validation rule that references the column ProductName in table Products, not just a "Worksheet!$firstcol$firstrow:lastcol:lastrow" format?

Hi Coupland,

Have you seen Dynamic Named Ranges that adjust with the number of rows you have? This might be what you are needing..

https://www.excel-easy.com/examples/...med-range.html
https://www.ablebits.com/office-addi...c-named-range/

Your question sounds like this is what you seek.