Order form - how to automatically round what a person inputs to a

[Just a young fella trying to have a go]

Hi all, I'm trying to make a product order form and I want to set it up so
that the "Qty" field automatically changes whatever a person enters into a
multiple of 6 units (all the products come in packs of 6 so customers have to
order 6 or 12 or 18 or 24 units - you'd be amazed how many people still try
to order 21 or 19!). So to avoid confusion and having to ring the customer
back to say 'um, 19 is not a multiple of 6, would you like 24 units
instead?', I want to set up our Excel order form so whatever they input in
the "Qty" column to automatically rounds to the nearest mulitple of 6 or
round up to the next multiple of 6.

I've tried the =MROUND function but that requires another column, so we'd
have to have a "I think I would like to order this Qty" column where the
customer enters in a number (e.g. 8) and then another column of "you need to
change your order to this Qty" which would display '6' or '12' units...
having two columns is going to create confusion again - customers would be
thinking 'um, how many am I ordering here?'.

Ideally, we'd have some 'hidden and automatically applied formula' so the
user can't delete the formula and whatever number they enter in the "Qty" box
for each product automatically gets rounded to a multiple of 6.

Any ideas please guys?

THANKS! Alex.

 

[Rick Rothstein \(MVP - VB\)]

What about using a drop down data list? In an out-of-view column (a hidden
one maybe), place the values 6,12,18,etc. Then highlight the Qty column and
click Data/Validation in Excel's menu. On the Settings tab in the dialog box
that appears, select List in the "Allow" field, put the absolute range for
the values you put in the out-of-view column in the "Source" field, and
check the two check boxes (you can modify the various message fields on the
other two tabs if you want). Click OK. The column will reject any typed in
number that is on the list but, more importantly, will allow the user to
select a (multiple of 6) value from the drop down list without having to
type anything.

Rick


"Just a young fella trying to have a go"

 

[Just a young fella trying to have a go]

Hi Rick,

Thanks heaps for your post and thoughts mate, I appreciate it. I had a look
at the drop down menu option but what's turning me off is having the pull
down lists always there - I also want to be able to print off this order form
so people can fill it in by hand if they want to. The problem with the pull
down lists is that the arrow and first number always appear. I'm trying to
get it so the field is blank at the start (so it also prints blank) and then
if people want to fill it in on their computers we're eliminating the chance
of them not ordering in a multiple of 6 by having the field automatically
round to the nearest multiple of 6. Does that make sense?

Thanks mate,

Alex.

 

[Rick Rothstein \(MVP - VB\)]

What about if you start the list off with a blank instead of a 6? By the
way, the people *can* type into the field without picking from the list...
and, if they don't type in a value that is on the list, they get a warning
message. That means only multiples of 6 will be able to be typed in or
selected from the list. Also, about the down arrow... move the active cell
to some other cell not in that column and no down arrow will be shown.

Rick


"Just a young fella trying to have a go"