List Data Validation and Green Triangle

MikeMcCollister

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,111
Office Version
  1. 365
Platform
  1. Windows
What is the error that the green triangle is returning?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Peter,

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

Mike
 

MikeMcCollister

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Here is a simple sample of what I am seeing.

1599738508291.png


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

Regards,

Mike
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,111
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Yes, the data is in a table.

1599739449964.png


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

Mike
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
.. 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)

1599739515379.png
 

Watch MrExcel Video

Forum statistics

Threads
1,113,980
Messages
5,545,322
Members
410,676
Latest member
M0J0jojo
Top