Automatic Checkbox w cross-sheet & lookup

kym4639

I'd like to create a series of formulas in a sheet that will automatically check a checkbox column if certain conditions exist.

The 2 sheets are ATTENDANCE and SIGN IN. The Sign In sheet has attendee names in the first (non-Primary) column (in the order that the attendee signs in, not alphabetized) and has a date in another column. The Attendance Sheet has a list of all potential attendee names in the first (non-Primary) column and each column after that is associated with a date. Those columns are checkbox columns, the intent being that if a person attended on a certain day, the appropriate column will be checked. I have a row at the top of the Attendance sheet that lists the actual date in date format so that the formulas have a fixed reference to pull from.

In order to satisfy the conditions tocheck the box, I need to first check the Sign In sheet for the attendee name. Then the formula needs to look at the date column to determine if the date listed on the Sign In sheet is the date at the top of the column on the Attendance sheet.

In my mind, this is an INDEX/COLLECT situation with something layered on top of it to force the checkbox, but my current formula is returning an #UNPARSEABLE error. The formula that I'm using at the moment is below.

=INDEX(COLLECT({Sign In: Dropdown thru Rehearsal Date}, {Sign In: Dropdown column}, [$Dropdown Name]6, {Sign In: Rehearsal Date column}, [May 8]$1), 1)

Thoughts?

 

L_123

your dollar sign is in the wrong location in dropdown name

[$Dropdown Name]6 should be $[Dropdown Name]6

Paul Newcome

The attendance sheet... Are they checkbox columns or date type columns? If they are checkbox columns, any date entered manually regardless of format/pattern will not be usable as a date within a formula.

 

Can you provide some screenshots with confidential/sensitive data removed, blocked, or replaced with "dummy data"?

kym4639

The attendance sheet is populated with checkbox columns. The only outlier is the very top row, which currently has dates. Sounds like you're saying that the top row isn't going to function the way I want it to because of the column format.

And when I made the syntax change (not reflected in the screenshot below), my new error is INCORRECT ARGUMENT.

 

kym4639

Oh. I see the button now.

Paul Newcome

Ok. I just want to make sure I have your current process understood...

 

You have a master list of all possible attendees. This has the names in one column and a series of checkbox columns for different dates.

 

You also have a sign-in sheet. This has a single column for names and a single column for dates.

 

You want to look at the sign-in sheet to see if a name has a date next to it. You then want the box to check on the attendees listing sheet in the date column that matches to the date in the sign-in sheet.

.

Does this sound correct? If so, we can get away with some slight tweaks to your sheet and a fairly basic IF statement.

 

If not, please feel free to correct me. I'd rather work out these details now than in the middle of building out a solution.

L_123

I would double check each of your other sheet references. Smartsheet has been deselecting mine when I have created them, and I am forced to go back and remake them over the last few weeks. My guess is that one of your ranges got deselected and it is giving you that error because the range sizes don't match.

kym4639

I have a master sheet that contains possible attendees. This sheet has not previously been part of my question, but it is the REGISTRATION Sheet.

The names on the REGISTRATION Sheet are then manually copied into a dropdown on the SIGN IN SHEET. The SIGN IN Sheet is populated each day by a form. The time/date stamp information is extrapolated so that there is a column on the SIGN IN Sheet that just contains the date. That column is a date column. Names only show up on the SIGN IN Sheet if they attend. 

My ultimate question is about the ATTENDANCE Sheet. I want the ATTENDANCE sheet to check the SIGN IN Sheet to find both attendee name and a specific date. If the formula finds both, it should check the checkbox. If not, the checkbox should remain unchecked.

 

These are just some small clarifying points. You seem to have captured everything else accurately.

 

 

Paul Newcome

Ok. This should be straight forward enough. It seems like you have everything set up properly with the exception of the dates being in the checkbox columns. Here is my suggestion for this...

 

Add another 3 ROWS underneath of the date row. We will indent these 3 rows underneath of row 1 so they can be collapsed and keep the sheet looking cleaner. It will look something like this...

 

- Row 1

    Row 2

    Row 3

    Row 4

.

In row 2 you would put the year, row 3 would be the month, and row 4 would be the day. These numbers will match the year, month, and day of that column's date.

 

Then in your current formula of...

 

=INDEX(COLLECT({Sign In: Dropdown thru Rehearsal Date}, {Sign In: Dropdown column}, $[Dropdown Name]6, {Sign In: Rehearsal Date column}, [May 8]$1), 1)

 

we would take the bold portion where you are attempting to compare to a date and replace it with a DATE function.

 

=INDEX(COLLECT({Sign In: Dropdown thru Rehearsal Date}, {Sign In: Dropdown column}, $[Dropdown Name]6, {Sign In: Rehearsal Date column}, DATE([May 8]$2, [May 8]$3, [May 8]$4), 1)

.

This will allow you to reference the date very easily since each portion can be a cell reference, and you can then hide those date rows of 2, 3, and 4 by collapsing them underneath of their parent row 1.

.

Does all of that make sense?

kym4639

Thank you for this. I'm still getting an error, though. 

 

Current formula:

=INDEX(COLLECT({Sign In: Dropdown thru Rehearsal Date}, {Sign In: Dropdown column}, $[Dropdown Name]57, {Sign In: Rehearsal Date column}, DATE([September 11]59, [September 11]60, [September 11]61)), 1)

Paul Newcome

Double check your row numbers in your DATE function. In your formula, you have 59, 60, and 61, but on your sheet they are in rows 66, 67, and 68.

 

Make sure you are locking in all of the references that you don't want to update when dragfilling.

kym4639

Ugh. I am so stumped by this.

Current formula:

=INDEX(COLLECT({19-20 Sign In: Dropdown to Rehearsal Date}, {19-20 Sign In: Dropdown}, $[Dropdown Name]57, {19-20 Sign In: Rehearsal Date}, DATE([September 11]$66, [September 11]$67, [September 11]$68), 1))

Paul Newcome

What is in each of your ranges?

 

kym4639

See below

Paul Newcome

Ok. This makes things a lot more clear. So there are no checkboxes on the source sheet. You are just looking for name and date match to check a box.

 

Add/change the bold portions below.

 

=IF(ISDATE(INDEX(COLLECT({19-20 Sign In: Rehearsal Date}, {19-20 Sign In: Dropdown}, $[Dropdown Name]57, {19-20 Sign In: Rehearsal Date}, DATE([September 11]$66, [September 11]$67, [September 11]$68), 1))), 1)

kym4639

What am I missing?

Current formula:

=IF(ISDATE(INDEX(COLLECT({19-20 Sign In: Rehearsal Date}, {19-20 Sign In: Dropdown}, $[Dropdown Name]81, {19-20 Sign In: Rehearsal Date}, DATE([September 11]$90, [September 11]$91, [September 11]$92), 1))), 1)