Any way of adding formulas to popup menus or tick boxes?

"I can do the math I need to do to a cell but how do I get the pop up or tick box to talk to that cell?? "

You don't.

You get 'that cell" to 'read' "the pop up or the tick box" by placing a formula in "that cell."

Using your example above:

H2 is your "pop up menu" cell. It has three items, 'none', "Long", and "Short"

N2 is "that cell." It contains the formula shown.

In cell N2, the first IF gets the content displayed in H2.

• If H2 is set to "Short", the comparison 'H2="Short" returns TRUE,
  • IF passes control to the first PRODUCT formula, which does its calculation and displays the result in N2, then exits.
• IF H2 is set to "Long", the comparison 'H2="Short" returns FALSE,
  • IF passes control to the second IF
  • the second IF gets the content displayed in H2.
    • The second IF's comparison, H2= "Long" returns TRUE, and passes control to the second PRODUCT, which does is calculation, displays the result in N2, the exits.
• If H2 is 'blank' both the first comparison and the second comparison return FALSE
  • the first IF passes control to the second IF
    • the second IF places a null string ( "" ) in N2, then exits.

Regarding the tick box:

What content is to change, depending on the state of the checkbox in P2?

If it the the word displayed in O2, use this formula in O2, replacing "checked" with the word you want displayed when the box is ticked:

O2:   IF(P2,"checked","open")

Regards,

Barry

Impossible to answer until you provide more detail regarding what you want.

What action will the user take?

What will be the result of that action?

Pop-up menus and checkboxes are data entry devices. Data ('true' or 'false' in the case of checkboxes, one value chosen from a list of values in the case of popup menus) is entered into the cell by clicking th box or by choosing a menu item. That data may be read by a formula in another cell, and the data can be used by the formula in that cell to create a value to be displayed and available in that cell.

How that is done depends on the details of the data provided and the data to be produced.

Regards,

Barry

Hi Gaz,

You can use a popup or checkbox value in a calculation in another cell that has a formula. This may give you what you are looking for. A cell can hold a formula or entered data but not both.

quinn

Hi Gaz,

It is still not clear to me what you want.

To be clear, a dropdown or popup menu cannot hold a calulated value.

Checkboxes have either "FALSE"(unchecked) or "TRUE"(checked) as their value. You can use IF to test the value and deliver a calculation to a cell with a formula. So your check box in Column P could be tested so that a formula in N shows either "Open" or a calulated price.

quinn

"Tried to do the same theory with the pop ups but getting "Argument 1 of IF expects a Boolean, but cell O2 contains a string"

Checkboxes contain a Boolean (value)—either TRUE or FALSE.

In the IF statement examples above, the first argument is a comparison. That comparison returns a Boolean—TRUE if the comparison is true, FALSE if it is not.

2=1*2 returns TRUE

5-4>2 returns FALSE

A1="Joe wins" returns TRUE if cell A1 contains the text 'Joe wins' and returns FALSE if cell A1 is empty or contains anything other that that exact text.

The syntax for IF is: IF(expression,if-true,if-false)

expression can be any expression that will return a Boolean (true or false)

if-true tells the formula what to do if expression returns true

if-false tells the formula what t do if expression returns false

if-true and if-false can be specific values or can be formulas

The result is calculated and displayed in the cell containing the formula.

Here's an example:

The formula is placed in cell B2:

B2: IF(A2="Joe wins","Congratulations, Joe!","That's too bad, Joe. Please try again.")

Unfortunately, this formula will display the second message whenever A2 contains anything other that "Joe wins" (which we want), including any time when A2 is empty. One way to avoid that is to wrap the whole formula shown above in another IF statement. expression in this case checks for lack of content in A2, and calls the formula above only if there is something in A2.

B2: IF(A2="", "", IF(A2="Joe wins","Congratulations, Joe!","That's too bad, Joe. Please try again."))

"I just want to have a couple of options with different math and outputs to another cell. This is doing my head in now and I bet I' doing yours in. lol "

Actually, what is 'doing my head in' is the lack of specific detail in your posts, forcing us to guess why your formulas are not working. Including a copy of the not-working formula in your post would make the diagnosis and repair much easier. Including a screen shot, showing the formula, as you did in our first reply to Quinn, is also a great help to our understanding on what you want to happen (and to why it is not happeining).

Regards,

Barry

Hi Gaz,

Adding to Barry's reply:

Barry wrote:

The syntax for IF is: IF(expression,if-true,if-false)

I think of that as if, then, else.

If it is raining, then stay at home, else go for a picnic.

Regards,

Ian.

Hi Gaz,

I am thinking something like:

IF(P2,<your formula>,"open")

What this says is if the checkbox in P2 is checked, use <your formula>, otherwise return "open"

quinn