Restricting input to a user

ITWater

Board Regular
Joined
Jan 10, 2006
Messages
59
Hi

Maybe not as simple as the title would suggest but here goes.

I have a list of depots and a number, either 10, 14, 19 or 28 to the right of the depots. This relates to the size of tanker we can access the depot with...i.e. depot x can be accessed using a 28m3 tanker, the user inputs the order for that particular depot, lets assume they want 4 loads, I need an order for 4x28=112. Because we sometimes change the tanker volumes, i.e. a 28 used to be a 27, some of the old school place an order for 108 i.e. 4x27.

This is creating a problem because this order form gets imported in csv format into an asp.net database which splits the order into the relevant tanker acces size, when 108 is ordered (by old school) I end up with 3.85 loads instead of 4 whole loads (we dont do part loads)

So, lets assume column a=depot, column b=access size (28), column c = order.....I need a way of validating if the user order (column c) is divisible by column b into a whole number, if yes then allow cell entry, if no then display dialogue box informing the user of the inconsistency and preventing input.

I'm quite familiar with cell validation and use it quite often but for the life of me can't work out how to express the above in a custom formula....to add to the confusion some users type 28m3, again the database will not pick this up, so I need to ensure they only enter numbers.

Any help would be greatly appreciated.

Thanks
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
you can check by saying :

IF INT(LOADS)=LOADS

you can also use ISNUMBER to check if the input is correct
 
Upvote 0
Thanks for the quick reply...

Lozzabloke - this errors, as the cell the validation is in is also the check cell....column b is the order denomination i.e. 28 and cell c is the order, the validation exists in c, I think it produces a circular reference....


Fairwinds - Same as above, it produces a circular reference...

I suppose I could check for a divisible number in another cell and get the validation to look at that????

Any thoughts
 
Upvote 0
I did the following:

B1=Load (112 or 118)
C1= size (28)
D1= B1/C1 (3.85 or 4)

Validation for B1 is:
=INT(D1)=D1

This seems to work
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top