# Textbox/Formated Field and LinkedCells [closed]

Hi there, first-timer here.

I am working on an RPG charsheet in calculator and i've hit a wall.

I've got these textboxes/fields (tried both, just in case), where players could view their satus, linked to cells from where calculator can calculate other stats.. All working well untill i tried to format a cell which would get a value from a box.

So.. 1 box/field is linked to a formated cell B15 (+0;#;#) [+1234;#;#]
Another cell [C15] takes the value inputed in this cell and use it in the following formula:

=IF(B15>5,B15-5)

Say B15 = +3; C15 displays blank as expected // Say B15 = +6; C15 displays +1 as expected

If i enter value directly into cell, calculations work perfectly. But getting the value from linked textbox/field does not work so well:

Say B15 = 6; C15 displays +1 as expected // Say B15 = 3; C15 displays: 2 (3 - 5, without "-" signal)

I've noticed that when i enter the value from textbox, the cell gets ( 'value ) instead of plain (value). I understand why this would be a problem, hence why i tried using formated fields (which didnt work also, and would not display cell formating). I also noticed that through other textboxes (these ones not linked to formated cells) cells get plain (value). Removing the formating from the cell, however, did not fix the problem.

Is there a way to solve this?

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-26 22:21:24.032908

Sort by » oldest newest most voted

Well, a 'Text Box' will pass text to the linked cell (B15), not a number. Thus the number format of B15 is meaningless. What yu tried by =IF(B15>5,B15-5,{alternative missing}) should be done by =IF(VALUE(B15)>5,VALUE(B15)-5,{alternative missing}).

The second call of VALUE() may be omitted because automatic type conversion will take place for the operand position in front of "-". On the other hand ">" is an operator also capable of handling 2 strings and therefore the 5 which you suppose to be numeric will be automatically converted to "5". Holy Automatisms!

more

Great! That did it! I was hoping it would be something like this... but honestly did not think of a VALUE() function

Thanks!!

( 2014-06-26 10:04:11 +0200 )edit