Fillable Cells, but altering either or doesn't affect the formula?

Question

Level 1

32 points

Fillable Cells, but altering either or doesn't affect the formula?

Hello, I swear that before I used a spreadsheet where if I chose to say enter data in the "Lbs" cell rather then "Kgs" in cell it wouldn't delete the formula that was in the cell so that essentially if I put in a weight into the "Lbs" cell it would automatically populate the kg equivalent in the "Kgs" cell or vice versa. Furthermore, after I did that I could enter data in the "Kgs" cell it would show up the equivalent in the "Lbs" cell all the while not effecting the underlying formula in each cell? Please advise!

Posted on Nov 27, 2019 2:45 PM

Reply

Answer 1

Nov 28, 2019 5:08 PM in response to 1234x

I would set it up like this

Enter number values in column A. Column B is a Pop-Up Menu.

This formula is then copy and pasted into column E, and the “kg” and “lb” arguments in my CONVERT formula flipped.

Nothing to it. Took longer to write this post than it did to code the sheet.

I don’t see how they could “add this feature somehow”. No spreadsheet application in existence can do what you are wanting to do, as the formula is the cell contents. You can’t start typing values into filled cells without overwriting what is already there. It’s just not possible, the way spreadsheets are currently designed, and I doubt that’ll change in my lifetime.

Reply

Answer 2

t quinn

Level 5

7,043 points

Dec 3, 2019 4:02 PM in response to 1234x

Hi 1234x,

I haven't tested the whole formula but CONVERT() on my machine does not like capital letters.

CONVERT(A2,"lb","kg") works but "Lb" and Kg" both failed. I suspect "LB" and "KG" failed for the same reason.

quinn

Reply

Answer 3

t quinn

Level 5

7,043 points

Nov 28, 2019 4:48 PM in response to 1234x

Hi 1234x,

You could do this with 3 cells. 1 input and 2 to evaluate the entry and the other two to determine if it is pounds or kilos and to translate if needed. Could be a little complicated.

quinn

Reply

Answer 4

1234x Author

Level 1

32 points

Dec 3, 2019 12:47 PM in response to Bismarck2387

Thanks for that, any idea why the following modification is not working? Thanks its appreciated

Reply

Answer 5

SGIII

Level 8

35,504 points

Nov 27, 2019 5:06 PM in response to 1234x

We've all dreamed, at times, that we could do that. Wouldn't it be nice! But alas, if you input a value into a cell that will overwrite any formula that is already in that cell. And vice versa.

SG

Reply

Answer 6

1234x Author

Level 1

32 points

Nov 28, 2019 4:50 PM in response to t quinn

They really should add this feature somehow. Thanks guys.

Reply

Fillable Cells, but altering either or doesn't affect the formula?

Similar questions