Changing +/- value of input to one cell based on selection from list in another cell

[mnr45701]

Hello. infrequent user here.

I am setting up a simple inventory of a single item. I made add or delete from the inventory on any given day. I have a Quantity on Hand cell at the top of a column which is simply the sum of the cell values below it. I know that I can put a NEG sign before a numeric entry and it will properly subtract that value from the Quantity on Hand. What I would like to do is remove the potential for a wrong sign by determining the =/- value of my numeric input based on whether the text chosen from my drop down list in the preceding cell is "add to quantity on hand" or "subtract from quantity on hand".

Any ideas? I've run through the forum and didn't find anything, and I've about pulled out all my hair trying to understand the HELP files and online explanations.

Cheers,

Megan

 

[mnr45701]

Sorry for the typo above. I MIGHT add or delete, should say the second sentence.

 

[mikerickson]

You could do this with DataValidation. If A1 holds the "add" or "subtract" and the amount is entered in B1
Use this formula to put validation on cell B1.

=(B1*(IF(A1="Add",1,-1)) >=0

 

[mnr45701]

Thanks for the reply. I just tried to apply that--and I pretty much get the syntax--but Excel 2003 keeps telling me that I've made a mistake. I was pretty careful to change the applicable cell references and yes I am entering in Data Validation and not the cell itself. =(B9*(IF(A9="USED",1,-1))>=0 is the cut/paste of the formula I entered. So what idiot check am I missing here? Megan

 

[mnr45701]

Megan again: So I toyed with the syntax a bit and Excel accepts the formula and the arguments are in order but the data validation still does not apply a change in sign through the multiplication operations. My formula is currently =B5*(IF(A5="USED",-1,1)) Note: the >=0 portion would not allow the formula wizard to accept the formula so I dropped it out. Also, if I simply enter a negative value in B5 the formula at the top of column B performs properly so I'm confident there's not a problem there. Any ideas? Thank you in advance. Cheers, Megan

 

[mikerickson]

Validation won't change the sign, but it will alert you to when you failed to enter the sign.