Link to home
Start Free TrialLog in
Avatar of Thomas
ThomasFlag for Malaysia

asked on

Specifie Excel Row as negative

Hi,

I have a problem with excel that sounds simple but I can not find a solution and hope someone might have a solution for me.

I try to specify a row in excel as negative numbers. For example I like to specify row B as being negative. so that when someone types in a number into row B it is automatically made negative. So if someone would type in 100 in an cell in row B it would change it to -100.
I found some suggestions but nothing that does it automatically. It is a sheet that calculates inventory and several employees have access. so if someone would forget to type the minus sign in front of the negative number then it would mess the inventory up. Therefore, I look for a code, formula or something that allows me to check any value typed into a specified row and if it is not a negative number it should automatically convert it to -

Can this be done?
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Look Up "Data Validation" for your version of Excel. Usually A formula needs to reference outside cell. The validation allows it to be internal with rules and popup messages for data that does not validate.
You can use a formula if (entry)< 0 then it validates.
There are some slick validation techniques in the help section. Always more than one way to enter data.
I hope this helps
Data-Validation.jpg
Avatar of Thomas

ASKER

I looked for a solution for yours and other newsgroups told me it can not be done. You made it happen with a simply 5 code VB. You are my hero, thank you very much.
If you always have numbers in column B and never have non-numeric then you can reduce the code by deleting lines 2 and 4