conditional format ?

%3CLINGO-SUB%20id%3D%22lingo-sub-1322287%22%20slang%3D%22en-US%22%3Econditional%20format%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322287%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20get%20a%20cell%20to%20with%20a%20manually%20entered%20value%20to%20automatically%20turn%20to%20a%20-(minus)%20value%20based%20on%20the%20criteria%20of%20anther%20cell%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1322287%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322367%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20format%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629337%22%20target%3D%22_blank%22%3E%40MWill74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20Visual%20Basic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322528%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20format%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629337%22%20target%3D%22_blank%22%3E%40MWill74%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20it's%20can%20be%20done%20using%20conditional%20formatting.%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20the%20value%20in%20the%20column%20B%20is%20greater%20than%20or%20equal%20to%202%2C%20the%20cell%20formatted%20as%20(-).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Conditional%20Formatting.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185432i7DB3B0FCC354F775%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Conditional%20Formatting.png%22%20alt%3D%22Conditional%20Formatting.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322602%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20format%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322602%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629337%22%20target%3D%22_blank%22%3E%40MWill74%3C%2FA%3E%26nbsp%3B%20for%20numerous%20reasons%20I%20think%20you%20are%20better%20off%20with%20an%20additional%20column%3A%3C%2FP%3E%3CP%3EEntered%20value%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Use%20Value%3C%2FP%3E%3CP%3E%26nbsp%3B%5Buser%20enters%5D%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3Dif(a2%26gt%3B%3D2%2C%20-a2%2C%20a2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20addition%20to%20being%20an%20easy%20solution%2C%20it%20also%20preserved%20the%20integrity%20of%20what%20the%20user%20enters.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322640%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20format%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322640%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629337%22%20target%3D%22_blank%22%3E%40MWill74%3C%2FA%3E%3C%2FP%3E%3CP%3Ewas%20trying%20on%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MWill74_0-1587412648128.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185442iA20DA7DCF67776C5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22MWill74_0-1587412648128.png%22%20alt%3D%22MWill74_0-1587412648128.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322714%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20format%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629337%22%20target%3D%22_blank%22%3E%40MWill74%3C%2FA%3E%26nbsp%3B%20I%2FWe%20still%20don't%20know%20WHY%20you%20are%20trying%20to%20do%20this%20so%20a%20thought%20came%20to%20me.%26nbsp%3B%20If%20for%20example%20the%20only%20correct%20values%20they%20should%20enter%20are%20between%20-10%20%26lt%3B%20x%20%26lt%3B%201%20and%20your%20goal%20is%20to%20make%20an%20'obvious'%20incorrect%20manual%20entry%20of%20let's%20say%208%20become%20-8%20then%20you%20should%20really%20be%20looking%20at%20data%20validation%20instead.%26nbsp%3B%20That%20way%20you%20can%20prevent%20the%20user%20from%20entering%20an%20incorrect%20value%20like%20%2B8%20and%20even%20give%20them%20a%20warning%20message.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322683%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20format%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322683%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629337%22%20target%3D%22_blank%22%3E%40MWill74%3C%2FA%3E%26nbsp%3Bwith%20that%20you%20will%20only%20change%20cell%20format%20if%20Transaction%20Type%20%3D%20Issue.%20Conditional%20formatting%20will%20only%20check%20if%20formula%20is%20TRUE%2C%20it%20will%20not%20run%20the%20formula%20to%20replace%20the%20values.%20I%20agree%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B-%20you%20need%20additional%20column%20(you%20can%20always%20hide%20it%20or%20put%20it%20in%20another%20sheet).%20Either%20that%20or%20VBA.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

9 Replies
Highlighted

@MWill74 

That's Visual Basic

Highlighted

@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 (-).

Conditional Formatting.png

 

Hope that helps

Highlighted

@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.

Highlighted

@MWill74

was trying on this

 

MWill74_0-1587412648128.png

 

Highlighted

@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.

Highlighted

@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.

Highlighted

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.

Highlighted

@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.

Highlighted

@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.