Error in excel 2016 when adding a formula

user_6370369 0
Viewed 1175

I have an Excel formula that works fine in excel 2003 but in Excel 2016 I get the error when clicking on Data Validation.

"You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria"

My formula is

=INDIRECT("IQ!"&LEFT($T28, 2)&"11"):INDIRECT("IQ!"&LEFT($T28, 2)&11+$V28)

I had to do the formula like this, as the business users cannot update the formula themselves. I have a dropdown list in another sheet within Excel, which this refers to.

excel-2016
1 Answers

I'm curious why Excel doesn't accept the current format of Indirect range..

However, one workaround could be to create a named range:

enter image description here

And then refer to the named range (test1) in your data validation list:

enter image description here

user_45525290
Related