Google-sheets – Conditional formatting of duplicate values

conditional formattinggoogle sheets

In a full sheet range, I need a cell to be highlighted when a duplicate is typed, whether it is a number (phone number in this case) or a text string (name in my case). Both the new cell and the cell it is duplicating need to be highlighted.

I've tried the "countif" formula but to no avail. If it matters, I'm in a French version of Sheets.

Best Answer

This formula should do the trick. Assumes your range begins at a1 and ends at a100. Adjust as needed.

=AND(COUNTIF($A$1:$E$100,"="&A1)>1,not(isblank(A1)))

Related Solutions

Google-sheets – How to check if a cell is blank for Conditional formatting

With the range set to: B3:O1000

Try:

=AND(ISBLANK(B3),INT($A3)=INT(today()))

or even shorter:

=AND(ISBLANK(B3),$A3=today())

Google Sheets – Duplicate and Rename Sheet in One Action

This can be done with a script, using insertSheet method which takes the new sheet name and the template after which it's to be created. The first function adds a custom menu item when the spreadsheet is opened, the second performs duplication when that menu item is invoked.

As far as I can tell, the contents are copied over in the same way as they are with "Duplicate" function.

function onOpen() {
  var menu = [{name: "Duplicate and name", functionName: "dupName"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function dupName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var name = Browser.inputBox('Enter new sheet name');
  ss.insertSheet(name, {template: sheet});
}

Best Answer

Related Solutions

Google-sheets – How to check if a cell is blank for Conditional formatting

Google Sheets – Duplicate and Rename Sheet in One Action

Related Topic