Formula to add checkmarks from one sheet to another

ShanT

I am trying to have two sheets communicate with each other and I've tried using the Index Match formula with no success. Sheet 1 is an attendance check-in sheet based on a form submission. Sheet 2 is a high-level tracking form that needs to be organized in one row per person (whereas sheet 1 will have several entries per person).

Sheet 1

Sheet 2

Essentially check-ins from Sheet 1 need to be reflected on Sheet 2 and I think a formula is the way to accomplish this but not sure so hoping you can help. I currently have both sheets setup as a checkmark field.

Any help or ideas are appreciated!

heyjay

On Sheet 2, ProgramOverview Column

 = Index({X},Match(Name@Row, {Y}, 0))

X = Sheet 1, Program Overview Column

Y = Sheet 1, Name Column

ShanT

Thanks @heyjay for your quick reply. I am still getting #UPARSEABLE with this formula.

Paul Newcome

"@row" is case sensitive and should be all lower case.

heyjay

https://community.smartsheet.com/discussion/comment/447178#Comment_447178

I never knew that! Thanks!

ShanT

@Paul Newcome thanks. I clicked on the cell to auto-fill "@row" and it is still #UPARESEABLE. Any other ideas?

heyjay

https://community.smartsheet.com/discussion/comment/447187#Comment_447187

Can you post the formula that is giving you an error here?

Paul Newcome

@ShanT Are you able to provide a screenshot of the formula open in a cell in the sheet as if you are about to edit it?

ShanT

Yes see below. I am using the email@row vs. name@row to ensure it's accurate and we are not having varations of Joe vs. Joseph etc.. (these are self entered and can vary per entry).

Paul Newcome

You are using too many parenthesis. Also note that the range in the MATCH function should also be a cross sheet reference.

=INDEX({Column To Pull}, MATCH(Email@row, {Column Containing Email Address}, 0))

ShanT

Thank you for this - getting closer. I fixed that and now I am getting #INVALID REF

Paul Newcome

That particular error means that you have typed out a {Cross Sheet Reference} but did not follow the appropriate steps to create it. That could be because of a basic typo when trying to type out an already existing cross sheet reference or from not setting it up properly to begin with.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references