Formula to add checkmarks from one sheet to another
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!
Answers
-
On Sheet 2, ProgramOverview Column
= Index({X},Match(Name@Row, {Y}, 0))X = Sheet 1, Program Overview Column
Y = Sheet 1, Name Column
...
-
Thanks @heyjay for your quick reply. I am still getting #UPARSEABLE with this formula.
-
"@row" is case sensitive and should be all lower case.
-
-
@Paul Newcome thanks. I clicked on the cell to auto-fill "@row" and it is still #UPARESEABLE. Any other ideas?
-
-
@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?
-
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).
-
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))
-
Thank you for this - getting closer. I fixed that and now I am getting #INVALID REF
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!