Excel 2007 Data Valid. with list issue

The no fill cells (S5 & U5) are for direct entry. The color filled (light green - T5 & V5) do calculate but may be replaced with a direct entry if needed.

The decimal cells (S5 & T5) generate an invalid entry alert dialog for valid values above 1.12. Meaning if I key in 1.11 all goes well, if I key in 1.13 up to the maximum valid number of 2.00, I get the invalid dialog. If I select any of these numbers from the drop down list, the entry is accepted.

The whole numbered cells (U5 & V5) accept all keyed in numbers within the associated list. Picking from both drop lists work for all list members. It is the keyed in data for the decimal cells (S5 & T5) that fail for data > 1.12 If I convert the decimal list to whole numbers, (By keying in whole numbers into Lists!G5:G7) then it magically works again without changing any of the underlying formulas. Why is that & how to fix it?

The Ranges are dynamically created from the Min, Step, & Max entries on the Lists sheet. The TPR range is maintained by
[ =OFFSET(Lists!$AA$2,0,0,COUNT(Lists!$AA:$AA),1) ] Column AA , XYZ range uses the same formula with Z column nomenclature.

EDIT: Sorry for the confusion. I edited my post to hopefully improve understanding. I'll be uploading a new workbook with the above text included. I hope this is better than my last attempt. Apparently I was more fatigued than I realized.

Hello,

your problem description is really, REALLY hard to understand. Can you please try again and use some punctuation this time? Punctuation helps us figure out where one sentence ends and the next one starts. Your text all runs into one big mess.

When you refer to a cell, please use a cell address, like S5 instead of "the decimal cells". Describe what the problem is. What is "invalid entry"? What is showing in the cell? Why is it invalid? What should be showing instead?

Please help us help you and use a bit more care when writing your message.

cheers, teylyn

Originally Posted by teylyn

Hello,

your problem description is really, REALLY hard to understand.

I apologize

Can you please try again and use some punctuation this time? Punctuation helps us figure out where one sentence ends and the next one starts. Your text all runs into one big mess.

When you refer to a cell, please use a cell address, like S5 instead of "the decimal cells". Describe what the problem is. What is "invalid entry"?

Happens when I key in any number from 1.13 - 2.00 into cell S5 or T5

What is showing in the cell?

The number I keyed in displayed as selected focus remains on S5 or T5

Why is it invalid?

The data is valid but Excel throws up the Value is not valid dialog. The list being used to validate the decimal cells contains numbers from 1.00 to 2.00 in 0.01 increments. Picking from the drop list works as expected for all numbers 1.00-2.00.

What should be showing instead?

The number as keyed in with the selection moving on to the next cell (down if hitting return, right if TAB or Right key etc.)

Please help us help you and use a bit more care when writing your message.

cheers, teylyn

Thank you. That is much clearer.

The reason for the error message is probably in the tiny, tiny inaccuracies that Excel has with floating point precision (see here).

You can get rid of that error by rounding the number that is calculated in the Lists sheet. In cell AA2 of the Lists sheet use this formula

=IF(OR(G$6=0,AA2+G$6>G$7),NA(),ROUND(AA2+G$6,2))

and copy down. The numbers have now been rounded to exactly two decimals and floating point inaccuracies have been removed.

Now you can type any number in the valid range in the Track Master sheet without any error message.