List Data Validation and Green Triangle

[MikeMcCollister]

In my budget spreadsheet I use list data validation in my "type" column. The list options are as follows:

ATM
Deposit
EFT

This is a convenience to allow me to have a pulldown for the options. In the data validation I have the error alert disabled as I also want to allow for check numbers, like 1234 or 3456. This works fine but if a check number is added then a green triangle is visible in the upper left of the cell. I know that I can turn this off in Excel, but this setting is for all spreadsheets in Excel, not only the worksheet or workbook that I want these green triangles to go away.

Is there a way to have a data validation that allows for a pulldown for convenience but still allows for numbers without being considered invalid data?

Thanks,

Mike

 

[Fluff]

What is the error that the green triangle is returning?

 

[MikeMcCollister]

Fluff,

I get "Data Validation Error".

Mike

 

[Peter_SSs]

Edit: Ah, I see my question has been answered.
What does the green triangle drop-down tell you? Number Stored as Text? If so, can you have the cell formatted as General instead of Text?

 

[MikeMcCollister]

Peter,

I've tried setting for format to either General or Text and I get the same "Data validation error".

Mike

 

[MikeMcCollister]

Here is a simple sample of what I am seeing.

I consider A2 and A3 to be valid but not A3.

Regards,

Mike

 

[Peter_SSs]

I've tried setting for format to either General or Text and I get the same "Data validation error".

Yes, my suggestion was for my guess of the message being 'Number stored as text', not for a 'data validation error'.

If you only want these particular triangles disabled for this particular workbook, then I think you will have to use vba, but then any user of the workbook will need to have macros enabled for it to work. If you want to test, put this in the ThisWorkbook module of the workbook in question then close & reopen or move to another workbook and back.

Private Sub Workbook_Activate()
  Application.ErrorCheckingOptions.ListDataValidation = False
End Sub

Private Sub Workbook_Deactivate()
  Application.ErrorCheckingOptions.ListDataValidation = True
End Sub

 

[Fluff]

Is your data in a structured table?
That's the only way I can get an error flag & even then it's not the one you are getting.

 

[MikeMcCollister]

Yes, the data is in a table.

The list is A2:A7. I've named that range "Types".

Mike

 

[Peter_SSs]

.. then it's not the one you are getting.

I can reproduce that error (yes, in a structured table which is what it appears to be in the post 6 image)