Forum Discussion
Tyler Smith
Mar 27, 2019Copper Contributor
Check to see if number that is being input it divisible by another cell
Hello,
I am having trouble figuring this out, I'm not sure if it is even possible. So I have a range of cells that a customer will be inputting data, this data must meet a full container quantity. So lets say A1 has a 10 pc box quantity, then whatever number they input in B1:B5 has to be divisible by 10. Is there anyway to make this automatically happen or maybe make a prompt letting them know that the number they entered is not divisible by what is in A1?
Thank you!
Sridhar0311 the easiest is using FILTER() (which assumer you have the latest Excel)
I also took the liberty to format your data as a table but the formula could be done using row-column references instead:
=FILTER(Table2[ID],Table2[Position]=A$2,"")
4 Replies
Sort By
- TwifooSilver ContributorUse custom this data validation formula in B1:B5, with B1 selected:
=MOD(B1,A$1)=0
This may be your input message:
Enter a number divisible by the number in A1.
Lastly, this may be your error message:
The number you entered is not divisible by the number in A1. Please enter another number.- Tyler SmithCopper Contributor
This works great, I was way overthinking it. Thank you for the help!!
- TwifooSilver ContributorYou’re welcome.
- karthickrichardCopper Contributor
You could add a total/sum cell somewhere on the sheet and light up A1 using Conditional formatting if the total/sum of B1:B5 doesn't match the value in A1.
Step 1: Add the helper cell.
Step 2: Add the Conditional Formatting Rule