Limit Data Entry into Cell so that it is greater than entry in Previous Cell

[FRED_SHEET]

I have two cells for entering in integer values for anumerator and denominator to be used in a spreadsheet for adding Feet, Inches,and Fraction of an Inch for two or multiple tape measure measurements or steelrule measurements.The numerator and denominator determine the value of the fraction of an inch.

The value of the fraction of an inch is first determined by numerator integer entry into cells in column H. For discussion purposes less start with cell (H12).I am using conditional formatting to limit the entries into this column to integer numbers between 1 and 63.There is a statement for this column and cell stating these limitations.

The second part of the fraction of inch is determined by denominator integer entry into cells in column J for the denominator. For discussion purposes (J12). I am using conditional formatting to limitentries into this column to integer numbers 64, 32, 16, 8, 4, or 2. There is a statement for this column and cellstating these limitations also stating the DENOMINATOR MUST BE GREATER THAN THENUMERATOR.

Entries into the numerator column H (H12) and denominatorcolumn J (J12) are used to calculate the Inch Fraction Decimal Equivalent inColumn M (M12).This calculation must be less than one.

I would like some way to some way to prevent data entry inthe denominator column J (J12) to result in a fraction calculation greater than one other than a statement just warning of this fact.

Column H Numerator Limited to integers 1 thru 63 H12 for discussion purposes.
Column J Denominator Limited to integers 64, 32, 16, 8, 4, 2 J12 fordiscussion purposes
Column M Fraction Column H divided by Column J M12 for discussion purposes

Column M (M12) formula gives the Fraction DecimalEquivalent H12/J12 in decimal inches.

What can I do to keep the entry in cell J12 so that it is greater than H12 and still meet the conditional formatting requirements in cells H12 and J12 and NOT just rely on a warning statement.

Any thoughts.

 

[NiMip]

It's not clear to me whether you're already using Data Validation for your inputs, so that's the first place to look for limiting the available values you can type in a cell: http://www.excel-easy.com/examples/dependent-drop-down-lists.html

But, the issue is you can't limit the value of a calculation. You could say you only want the fraction result cell to be <1, but if you choose 32/8 as your inputs, which are both valid, the calculation won't care and it'll spit out 4 anyway.

You could make the fraction output tell you you've made bad choices with an IF statement: =IF(numer/denom<1,numer/denom,"Fraction must be <1, adjust your inputs").
You could also do something such as this, if you're wanting a more clear statement of what you can and can't use as inputs, using the Available Denominators range as your Data Validation list for the denominator. I've used the formula =IF(Numerator/RawDenominator<1,RawDenominator,RawDenominator&" is N/A (fraction > 1)") for the Available Denominators list

[TABLE="class: grid, width: 552"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Raw Denominators[/TD]
[TD="align: center"]Available Denominators
(Data Validation input range)[/TD]
[/TR]
[TR]
[TD]Numerator[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]63[/TD]
[/TR]
[TR]
[TD]Denominator[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]32[/TD]
[/TR]
[TR]
[TD]Fraction[/TD]
[TD="align: right"]0.96875[/TD]
[TD][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][TABLE="width: 194"]
<tbody>[TR]
[TD="width: 194"][TABLE="width: 194"]
<tbody>[TR]
[TD="width: 194"]16 is N/A (fraction > 1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][TABLE="width: 194"]
<tbody>[TR]
[TD="width: 194"]8 is N/A (fraction > 1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][TABLE="width: 194"]
<tbody>[TR]
[TD="width: 194"]4 is N/A (fraction > 1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][TABLE="width: 194"]
<tbody>[TR]
[TD="width: 194"]2 is N/A (fraction > 1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The last option I can think of, if you're wanting it to be physically impossible to enter in values that result in a fraction > 1, is to use a userform to enter your input, and create an error message if values are entered that don't result in <1 fraction. http://www.excel-easy.com/vba/userform.html

I hope that helps?

 

[pkdeimos]

Hi,

You may add "Data Validation" rule fro both H12 and J12.

1. For H12, "Whole number between 1 and 63"
2. For J12, "Whole number greater than H12". This rule will restrict the user from entering a value smaller or equal to H12. But it will not restrict from entering values other than 64, 32, 16, 8, 4, 2

Hope this helps.

Regards.

 

[FRED_SHEET]

I have two cells for entering in integer values for anumerator and denominator to be used in a spreadsheet for adding Feet, Inches,and Fraction of an Inch for two or multiple tape measure measurements or steelrule measurements.The numerator and denominator determine the value of the fraction of an inch.

The value of the fraction of an inch is first determined by numerator integer entry into cells in column H. For discussion purposes less start with cell (H12).I am using conditional formatting to limit the entries into this column to integer numbers between 1 and 63.There is a statement for this column and cell stating these limitations.

The second part of the fraction of inch is determined by denominator integer entry into cells in column J for the denominator. For discussion purposes (J12). I am using conditional formatting to limitentries into this column to integer numbers 64, 32, 16, 8, 4, or 2. There is a statement for this column and cellstating these limitations also stating the DENOMINATOR MUST BE GREATER THAN THENUMERATOR.

Entries into the numerator column H (H12) and denominatorcolumn J (J12) are used to calculate the Inch Fraction Decimal Equivalent inColumn M (M12).This calculation must be less than one.

I would like some way to some way to prevent data entry inthe denominator column J (J12) to result in a fraction calculation greater than one other than a statement just warning of this fact.

Column H Numerator Limited to integers 1 thru 63 H12 for discussion purposes.
Column J Denominator Limited to integers 64, 32, 16, 8, 4, 2 J12 fordiscussion purposes
Column M Fraction Column H divided by Column J M12 for discussion purposes

Column M (M12) formula gives the Fraction DecimalEquivalent H12/J12 in decimal inches.

What can I do to keep the entry in cell J12 so that it is greater than H12 and still meet the conditional formatting requirements in cells H12 and J12 and NOT just rely on a warning statement.

Any thoughts.

I am using data validation for column H (H12) and column J J(12) and the validation is working properly and you can only enter the proper numbers. I have decided to highlight the denominator cell J(12) pink if it doesn't meet the rule =$H$12>=$J$12. This will highlight to the user they are not meeting the requirement that the denominator must be larger than the numerator.

I am going to give further thought to your suggestions an see if I might make further changes.

Thanks for your help.