Formula for a checkbox

Emily Zeiger

Hi, I am trying to create a formula to check a box but continue to get invalid data and unparseable error messages. I need to reference another sheet. Here is what I am hopeful to do. If a branch number on sheet one matches a branch number on sheet 2 AND the sell date on sheet one matches the sell date in the summary on sheet two then the box will check. Any thoughts on how to make this work?

I appreciate any help!

rgochee

You should be able to use an Index Match formula.

=Index(range, match(search_value, range,[search_type]))

Something like below, I am using the Branch Number as the consistent between the sheet. If the Sell date on sheet one does not match the Sell date on sheet 2 it will return a false.

=if(index({sell date in the summary}, match([branch number on sheet one]@row,{branch number on sheet 2},0)) = [sell date on sheet one]@row,true,false)

Paul Newcome

When you say "in the summary on sheet two", do you mean a sheet summary field?

Are you able to provide screenshots with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

Emily Zeiger

This is the first sheet: responses are sent via a form. When a response is received, it is then mapped onto sheet 2.

Sheet 2 -- responses from sheet one show here and are pulled in via sheet summary pending the date of shipment from sheet 1. I want the check box under Y/N sell to auto check if an entry has been submitted for the shipment date selected.

Paul Newcome

Which sheet is the Date Summary field on? Which sheet are you putting the formula in? Based on your screenshots, both the Summary Field and the formula are on sheet 2. Or are your screenshots in reverse order?