Electronic Price List

J

Jono

Hi,
I am trying to create a template so people can create an electronic price
list.

I want to put restrictions on cells so that only valid values can be entered
in certain columns, for example:
Price - Must be greater than Zero
Category Code - Must contain a numeric value
Unit of Measure - Must match a valid list
Plus many others...

I would like to set up a worksheet so that users can cut and paste their
data in, but if the value is incorrect it highlights it in a different color.

I have tried setting up "validation" but if I cut and paste it will not
highlight the cell and would over write the values.

The template would be going to many end users so would need to be easy to
use (i.e. Cut and Paste Values would be beyond them!).

I think Excel might not do what I want in this instance? Any help would be
greatly appreciated.

Jono
 
J

jeff

Hi, Jono,

Excel can probably do most of what you want by using
VBA coding. You've found the problem with "validation":
one can "cut and paste around it" - otherwise it's great.

We can help you with some code if it doesn't get too
complex - and if you have changes or additions later,
you may have to learn VBA yourself to modify/add to
the macros. If, however, your validation needs become
too complex, perhaps Excel isn't the right tool.

Can you post all of your fields' validation needs to
give us more info?

"Price - Must be greater than Zero" - ok.
"Category Code - Must contain a numeric value" - can it
be positive or negative? can it have alphas
(ie "ABC-99-Z")? is -999999 ok?
and 999,999,999,999,999 ok?
"Unit of Measure - Must match a valid list" - ok, a
list box would probably work here.

Others?

jeff
 
J

Jono

Hi Jeff,

Thanks for the reply. I would really like to learn how to do this myself so
if you can give me direction that would be great. I'm IT literate, I don't
have any programming or script writing expereince though!

If you can get me started, I can probably finish the rest.

Thanks again for your help.

Jono

The template is for an electronic catalogue/price list and the fields are:

UNSPSC Category Must have either this or below Maximum 256 Characters Must
match list of about 10,000 items)
UNSPSC Category Code Must have this or above Maximum 8 Characters Must match
list of about 10,000 items)
Action Optional Must be ADD, DELETE or UPDATE
Dept Type Optional Maximum 256 Characters
Description Mandatory Maximum 256 Characters
Sup Part Num Mandatory Minimum 3, Maximum 256 Characters Must be unique
Mfg Part Num Optional Maximum 256 Characters
Mfg Name Optional Maximum 120 Characters
UOM Mandatory Maximum 256 Characters Must match list)
Currency Mandatory Maximum 3 characters Must match list)
Long Description Optional Maximum 4000 Characters
Saleable Quantity Optional Maximum 256 Characters
Supplier Category Optional Maximum 120 Characters
Price Mandatory Must be Numeric, greater than or equal to zero
Picture Optional Maximum 120 Characters, must have valid extension
Catalogue View Optional Maximum 40 Characters
Lead Time Optional Maximum 256 Characters
NRCA Optional Maximum 1 Character
Specifications Optional Maximum 700 Characters
Aliases Optional Maximum 700 Characters
Rich Content Specifications Optional Maximum 700 Characters
Comments Optional Maximum 4000 Characters
Supplier URL Optional Maximum 256 Characters
UOM2 Optional Maximum 256 Characters
Orderable Indicator Optional Maximum 1 Character
Contact Details Optional Maximum 256 Characters
Account Code Optional Maximum 40 Characters
Vat Rate Optional Must be Numeric
Vat Code Optional Maximum 10 Characters
Buyer Part Num Optional Maximum 700 Characters
Buyer Category Optional Maximum 256 Characters
 
Top