Apr 20 2020 10:57 AM
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
Apr 20 2020 12:13 PM
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
Apr 20 2020 12:39 PM
@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.
Apr 20 2020 01:14 PM
@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.
Apr 20 2020 01:28 PM
@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.
Apr 20 2020 02:44 PM
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.
Apr 20 2020 03:12 PM
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.
Apr 20 2020 03:13 PM - edited Apr 20 2020 03:17 PM
@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.