Formula to add checkmarks from one sheet to another

ShanT · 07/31/24

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 · 07/31/24

On Sheet 2, ProgramOverview Column

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

X = Sheet 1, Program Overview Column

Y = Sheet 1, Name Column

ShanT · 07/31/24

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

Paul Newcome · 07/31/24

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

heyjay · 07/31/24

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

I never knew that! Thanks!

ShanT · 07/31/24

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

heyjay · 07/31/24

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

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

Paul Newcome · 07/31/24

@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 · 07/31/24

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 · 07/31/24

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 · 07/31/24

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

Paul Newcome · 07/31/24

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

Formula to add checkmarks from one sheet to another

Answers

Help Article Resources

Categories