04-20-2020 10:57 AM
04-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.
04-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.
04-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.
04-20-2020 03:13 PM - edited 04-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:
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.