conditional format ?

[MWill74]

Trying to get a cell to with a manually entered value to automatically turn to a -(minus) value based on the criteria of anther cell

[#2]

@MWill74 

That's Visual Basic

[#3]

@MWill74

I think it's can be done using conditional formatting.

For example, if the value in the column B is greater than or equal to 2, the cell formatted as (-).

Hope that helps

[#4]

@MWill74  for numerous reasons I think you are better off with an additional column:

Entered value                 Use Value

 [user enters]             =if(a2>=2, -a2, a2)

in addition to being an easy solution, it also preserved the integrity of what the user enters.

[#5]

@MWill74

was trying on this

[#6]

@MWill74 with that you will only change cell format if Transaction Type = Issue. Conditional formatting will only check if formula is TRUE, it will not run the formula to replace the values. I agree with @mtarler - you need additional column (you can always hide it or put it in another sheet). Either that or VBA.

[#7]

@MWill74  I/We still don't know WHY you are trying to do this so a thought came to me.  If for example the only correct values they should enter are between -10 < x < 1 and your goal is to make an 'obvious' incorrect manual entry of let's say 8 become -8 then you should really be looking at data validation instead.  That way you can prevent the user from entering an incorrect value like +8 and even give them a warning message.

[#8]

An offline inventory tracker with table/drop down selections in all columns except for the QTY Allocated being a manual entry, originally had separate receive and issue tabs, then it needed rolled into one form. Just trying to keep QTY Allocated entry correct as receive+ and issue- to have the sum be accurate.

Thanks for all helpful insight and quick responses, much appreciated.

[#9]

@MWill74 

Does this do what you want?

The conditional number format displays the positive number in red and with a minus sign.  A defined name then creates a value whose magnitude is determined by the user input but whose sign is determined by the text.

[#10]

@MWill74  I think I understand what you are doing and checking for and again both of my comments/solutions will work.  Either you can just create a column for QTY and then another column they can't change (lock the cells) for QTY Change and use a formula =if([issue], -[QTY], [QTY])

or you can get fancy in the data validation and use a custom formula like:

=(2*([receive/issue]="receive")-1)*[QTY]>=0

or do neither of the above and require the QTY entered to ALWAYS be positive and in the formula where you want the sum to =0 / balance use a conditional formula to add or subtract there.