Is there a way to conditionally format duplicates if a value is in a column and if a box is checked? I need this to look through the whole sheet where there are many input values by the customer via a form.
No way to natively locate duplicates and apply conditional formatting, however, you can cheat.
Assuming you have a value column (we'll call itName) and checkbox column (let's call this oneBooked), we can create aDuplicatecheckbox column to the right and enter the following formula, copying it all the way down as far as you need:
You can then use conditional formatting to highlight rows/cells whereDuplicateis checked and hide theDuplicatecolumn when you are satisfied with the results.
这是有用的,但我有一个问题。将formula you listed above only look for Joe Bloggs? you have it listed in the countifs for "Booked 1". What I am looking for is an example where the duplicates box checks if any name is repeated in the name column with the condition that the booked column has been checked.
Basically all the Fred Nerks will have the duplicate checked since there is one Fred Nerks where the booked column has been checked.
We are looking to create a validation check in our sheet for accounts that have been used in the past for billing. New accounts will highlight to not the account needs to be confirmed.
No , the formula is not dependent on any particular value in theNamecolumn.
To break it down, the formula is calculating the count of values that match both these criteria:
the value inName1(i.e. the same row the formula is on) occurs more than once in theNamecolumn (i.e. it's a duplicate name)
theBookedvalue is checked (i.e. it's a duplicate booking)
If both criteria are fulfilled (i.e. you have a value that occurs more than once in theNamecolumn with the correspondingBookedcheckbox ticked), then theDuplicatecell (where the formula lives) is populated with a 1.
如果弗雷德Nerks有1000,但只有一行theBookedcheckbox ticked, it will not be shown as a duplicate. If you have 3 Joe Bloggs (or whatever name you chose to include inName) with all three beingBooked, then all three will be marked as duplicates.
If you copy the formula inDuplicatedown the column as far as yourNamevalues go, any new values added to Name will ensure a new row is added and the newDuplicatecell will inherit the formula from above.
Based on your initial description and subsequent explanation, it sounds like the above will do what you want. Unless I'm missing something...
Add a \"Created By\" column in your sheet and turn on \"Required smartsheet login to access your form\" in your form setting. So that whoever fills out the form, their email id will be automatically captured in \"created by\" column. This will replace your \"Requestor Name\" column. <\/p>