Data validation formulas not working

Question

Hi, 

I am trying to use formulas for data validation options (using the "=" key) and I keep receiving an error (no matter the formula):

"There is a problem with this formula. Not trying to type a formula? when the first character is an equal (=) or minus (-) sign, Excel thinks that is a formula (...)"

I receive this error even when I use something as basic as:

Validation>List>"=nameofatableIcreated". 

I have tried multiple formulas (Index, if, match), but every time I start with "=" I receive the error. 

Help?

Answer 1

Hi AlexGallegoT,

Thanks for posting back in our forum. We are glad to help you.

 Based on our further test, if the name range is created by the table, we need to use the way below to make it work in data validation. That is, not changing the name in Name Manager window, but changing the name directly in the name type box on the top left of the workbook.

1.Insert a Table > slect the Table value >Go to Name Box in front of left corner side>create a new name and press enter key to change a name of table.

2.for Data validation select the table value >Go to Data tab Ruben >select Data validation >in validation criteria under Allow select list>under source enter =table name and press enter its should be work.

 

Is that working now for you in this way?

We greatly appreciate your time and understanding.

Best Regards,

Waqas Muhammad

Answer 2

Hi Waqas,

Thank you for responses and ongoing follow ups. I did that from the beginning and I had named all my tables on the spreadsheet.

I gave up and found a separate way (using the "Indirect" formula). 

Thank you again for your help!

Answer 3

Please take us through the exact steps you are taking when you get the error. We need to be able to recreate the problem so we can try to find a fix.

Here are a couple of articles about doing validation, maybe you will see what you are doing wrong in one of these

@ Apply data validation to cells

https://support.office.com/en-us/article/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249#ID0EAADAAA=Windows

You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list.

@ **3 Types of Excel Drop Down Lists Compared** **** February 3, 2010

http://blog.contextures.com/archives/2010/02/03/3-types-of-excel-drop-down-lists-compared/

At a client’s office last week, I was selecting a pricing option from a data validation drop down list. The worksheet was zoomed to 75%, so we could see more of the data, and the person watching over my shoulder asked, “How can you even read that?” Unfortunately, there’s no way to make the font size bigger, which is one of the drawbacks of a data validation drop down list. There are other ways to create a drop down list though, and one of those options might work better in your worksheet. We’ll compare these types of Excel drop down lists:

   * Data Validation List

   * Form Control Combo Box

   * ActiveX Control Combo Box 

ET MR Validation.docx

Answer 4

Hello Waqas, 

Thanks for your response. I had already renamed all the tables/ranges and it did not work. I also tried with another workbook, but I get the same error. For instance, in the new workbook, I created a simple table and renamed it as "lists". I then tried to create the data validation cell and entered "=lists", but I still get the same error (the error occurs every time I use "="). 

Here is the screenshot of the office product information (this is a corporate account and not a personal laptop; I removed my email address the "Belongs to" field for confidentiality reasons).

 

Thank you again!

Answer 5

Hi AlexGallegoT,

Thanks for posting in our forum. We are glad to help you.

we test it on our side in data validation using default table name (like =Table1 etc.), we encountered same error message. So, we tried using the custom name to the same table range, for example, rename the “table1” to “name1”, then it works. We suggest you create a custom name to the table range and see whether you will have the same result.

For Index and If formula, as we haven’t got the detailed information how you create them, if the Index or if formula includes default table names, you can first change the default table name to custom ones, and check whether the index and if formula error can be fixed at the same time.

If issue still persist, to better address this concern, we would like to have your kind assistance to sharing the following information.

1.A screenshot of your Office Product Information. (Open Excel>File>Account, capture all the info under Product Information)

2.Please create a new workbook to test whether this behavior occurs with all the files.

Let us know if you need additional assistance.

Best Regards,

Waqas Muhammad