Inconsistent results

Question

I have inconsistent operation of data validation cells:

I have two columns, C3 and E3, using data validation. Both configured the same except for the range (C3 is "=age", E3 is "=inseam").

Validation data ranges are on a different worksheet.

Both ranges are numbers, both columns are numbers (same problem if defined as general).

If I manually enter a valid number under age, I get an error that age is out of range.

If I manually enter a valid number under inseam, it is happy.

Entering the same number using the drop down works for either.

Why does entering data manually on age not work, but does on inseam?

ALSO, if C3 = 2 and E3 = 15 and I test the C3 with =IF(C3=2,TRUE,FALSE) I get FALSE, if I test E3 with =IF(E3=15,TRUE,FALSE) I get TRUE.

What is wrong here?????????

Answer 1

In preparing the document to upload, I may have found the problem. For some reason age was a number stored as text. When I converted it to a number the tests work on age.

Now my problem is converting the whole column when it has not been populated with numbers.

There are procedures to bulk convert, but they only work if the cells have data.

Another procedure using =VALUE() doesn't work with data validated cells.

How do I bulk fix the cells without entering data in all of them?

Then I don't know why changing the column to numbers doesn't do it.

I also don't know how it got to this format problem.

Answer 2

Coming up HansV

Thanks

Answer 3

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.