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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 40 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!