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
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