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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
you can check by saying :

IF INT(LOADS)=LOADS

you can also use ISNUMBER to check if the input is correct
 

ITWater

Board Regular
Joined
Jan 10, 2006
Messages
59
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
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You could try ROUNDUP(loads, 0)
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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
 

ITWater

Board Regular
Joined
Jan 10, 2006
Messages
59
Thanks to all....

Used Lozzabloke's solution and it works....many thanks

:) :) :) :) :) :)
 

Forum statistics

Threads
1,141,312
Messages
5,705,682
Members
421,406
Latest member
kluna90

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
Top