0

I have found a couple of formulas/scripts for what I am looking for, but they are all locked for one sheet only, thus I will try to explain what exactly I need. I am unable to do it my self, because of my lack of knowledge, so I plead to you to help me out.

I want to have multiple sheets in Google spreadsheet, they can go up to sheet1 to sheet 15, not sure about the number, but they are going to be multiple, and will multiply in the future. So in all the sheets except Sheet1 I will have emails(data), the sheet1 Column D is where in the future I will enter emails, now I want to prevent duplicate entries in Sheet1 collum D by looking in all the sheets I have in that spreadsheet. This is an example if I did not make my self clear sheet1 column D I insert email, press enter, in a case in any other sheet there is that email, I want to show an error, and inability to input the email.

Let me know if this makes sense, and if this is even possible,

This is something that I worked with fine, but it is only for sheet one and column:

=COUNTIF($D$2:$D$D,D2)<1

Also, this is something I tried, to at least mark duplicates, and it did not work.

=countif(indirect("sheet2!A:A"),E2)>1
  • 1
    found a couple of formulas/scripts for what I am looking for, but they are all locked for one sheet. Try to modify them, look up whatever details you need for that (Like "How do I refer to another google sheet?"), then add those attempts to your post with expected results, actual results, error messages etc, and ask about those details. The way to learn this stuff is to start doing it yourself, not ask others to do the work for you. – user55949 Jul 25 '19 at 11:17
  • Welcome. Please edit the question to include the formulas. Comments are intended to ask for clarification and give transient information, all the relevant details should be included on the question. More details on webapps.stackexchange.com/help/privileges/comment – Rubén Jul 25 '19 at 14:16
0

Here is an example (for illustration) with Conditional Formatting and 3 sheets. The same formula can be used in Data Validation to disable entering of repeating emails.

=(COUNTIF(D:D,D:D)>1)+(REGEXMATCH(D1,TEXTJOIN("|",1,UNIQUE({INDIRECT("Sheet2!D2:D");
                                                            INDIRECT("Sheet3!D2:D")}))))

0

spreadsheet demo

  • Hi, this does look like a good way, but when I implement on mine, it just does not work, would you be able to take a look at my spreadsheet? Thank you – Max Owens Aug 22 '19 at 13:07
  • @MaxOwens sure, share a copy of your sheet – user0 Aug 29 '19 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.