Check Boxes and Vlookups

Checkboxes are in "input" only feature. said another way you cannot have checkbox AND a formula in a cell at the same time. You can conditionally print a check character by using the following:

A1=CHAR(10003)

Now how to apply this?

Let's start with one possible solution. Here's an overview:

I named the table on the left "Term Start", the one on the right "Final Schedule" and omitted the row that you merged-- merged rows cause problems an I avoid them.

Let's start with the table on the left "Term Start":

I added an extra column which you can hide later:

C2=IF(A2=TRUE, B2, "")

select C2 and fill down

Now for the table "Final Schedule":

I did not want to type in (or copy paste the repeated dates) so I took the liberty of automating that based on the my observation that they repeated in groups of 8.

B2=OFFSET(Term Start :: $B$2, FLOOR((ROW()-2), 8)/8, 0, 1, 1)

A2=IFERROR(IF(MATCH(B2,Term Start :: $C,0), CHAR(10003), ""), "")

select A2 and B2 and fill down as needed.

I hope this helps

What is missing that it:

It doesn't do exactly what I wanted, but it certainly answered my question

?

Hmm. It looked that exactly solved the problem as stated, other than the look of a checkmark vs a checked box.

Is it that you need to use that checkmark in a downstream formula, it is not just for display purposes? You can always add another column (which you'll hide later) that has a formula that converts the checkmark to 1/0 or TRUE/FALSE

=IF(A=CHAR(10003),1,0)

or

=IF(A=CHAR(10003),TRUE, FALSE)

Or you could use the checkmark directly as a condition in a lookup. or COUNTIF, or a number of other functions.

Using the Checkmark directly rather than converting it in an auxiliary column would be my choice. Be sure to treat it as a string character, as opposed to a logic condition.

Jerry

You might consider using Applescript as well as formulas. You can set/reset checkboxes with scripts and do a host of other things. Triggers, which require a script running in the background, may be possible depending on what you need to do. Checkboxes are difficult in that you cannot change their value in Applescript, you have to change the format to something else, set the cell to TRUE or FALSE, then change it back to a checkbox. If this happens fast, the checkbox flickers. But if you can tolerate a delay, it works pretty well and there is no flicker.

Here is a simple script that sets the checkbox in B2 to the value given in the checkbox in C2. The delay is 2 seconds or less. Copy this script to AppleScript Editor, set up a new Numbers document with checkboxes in cells B2 and C2 of Table 1 and then hit Run on the script. It will run in the background until you hit Stop.

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1

repeat

set format of cell "B2" to automatic

set value of cell "B2" to value of cell "C2"

set format of cell "B2" to checkbox

delay 2

end repeat

end tell

You'll also want to set the text color of cell B2 to white so you don't see it flash "TRUE" or "FALSE".

This is just a simple demo to give an idea of something you can do. In the end it would not be run from the AppleScript editor.

Note that the script is separate from the Numbers document. If you use the document on another computer, it also needs to have the script.

Scott Odgers wrote:

In reply to Wayne's ?:

I was trying to get to a different solution that required multiple lookups, but if you can't use the check box in a lookup, then my plan won't work. But, I still found your post helpful and informative. That's why I marked the question answered.

Even after all the discussion, I'm not clear on your objective. Spreadsheets don't do sequential logic, it's all parallel logic. But, there's nothing sequential in your description except for your use of the term "trigger" which I'm not sure I understand the meaning of in your context.

I think you're giving up too easy, but then maybe that's because I don't yet get the problem.

Jerry

Tell us how you would like the checkboxes to work, I'm not understanding what the problem is. Wayne made what appeared to be a good solution that used checkmarks instead of checkboxes. What else do you need the spreadsheet to do; how would you like the checkboxes/checkmarks to work?