Ask Your Question
0

Textbox/Formated Field and LinkedCells [closed]

asked 2014-06-26 05:42:56 +0100

gugabfigueiredo gravatar image

updated 2014-06-26 05:57:40 +0100

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted
0

answered 2014-06-26 09:49:22 +0100

Lupp gravatar image

updated 2014-06-26 09:50:02 +0100

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!

edit flag offensive delete link more

Comments

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

Thanks!!

gugabfigueiredo gravatar imagegugabfigueiredo ( 2014-06-26 10:04:11 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2014-06-26 05:42:56 +0100

Seen: 257 times

Last updated: Jun 26 '14