Named range over multiple worksheets?

[BLaCKouT]

Hi folks,

I'm trying to check for duplicate entries within a workbook. It's over multiple worksheets as the end-user wants to create a new worksheet for each month.

The range I want to check is C2:C500 on all worksheets.

I figured the easiest way to do this would be to set up a named range to cover these cells:

=Sheet1!$C$2:$C$500

and apply the following conditional formatting to cells within the range:

=(COUNTIF(checkingrange,C2)>1)

So that duplicates will be highlighted as soon as they're entered.

As you can see, the current named range only covers Sheet1.
Is there a way of making it check all sheets without naming them?

The user is currently manually inserting sheets each month, and I don't want to a) pre-insert sheets for the user, or b) rely on the user inserting sheets with the correct name for the named range to work.

Code that would apply to all worksheets irespective of their name would be very helpful. Can this be done?

Many thanks in advance,
B.

 

[xld]

    For Each sh In ActiveWorkbook.Worksheets
    
        With sh.Range("C2:C500")
    
            sh.Activate
            .Cells(1, 1).Select
            .Parent.Parent.Names.Add Name:="'" & sh.Name & "'!checkingrange", _
                                     RefersTo:=.Address(, , , True)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=(COUNTIF(checkingrange,C2)>1)"
        End With
    Next sh

 

[BLaCKouT]

Thank you for that, xld, but I was hoping to avoid VBA for this one if at all possible.
The security level is set to 'always ask' in here, and people using my workbooks tend to click 'disable macros' out of caution/puzzlement, then wonder why the functionality disappears. VBA seems to make people nervous if they don't understand it

The guys that will be entering the data are welders. And while I have nothing against welders, I know these guys, and I know they're non-Excel-proficient. I wanted to make the file as self-contained and bulletproof as possible.

 

[xld]

I don't think you can apply CF over multiple sheets, If you group the sheets, CF is disabled which suggests that you c an't.

 

[BLaCKouT]

I found a workaround.

• I pre-entered the worksheets for the rest of the year (they'll have to live with that), and an extra, thirteenth, sheet.
• A2:A500 on the extra sheet references C2:C500 in January's sheet
• B2:B500 references C2:C500 on February's sheet, and so on for 12 columns.
• A2:L500 on the extra sheet then contains all of the data entered in each column C. I named this range CheckRange.
• I then applied conditional formatting to each of the 12 column C's, which would highlight the cell if the contents appeared more than once in CheckRange.
• The extra worksheet gets hidden.

• Ergo, when a duplicate is entered anywhere in the workbook, both entries are highlighted.

Thank you again for your help xld, it's by working through the solutions that aren't always practical, than I can get to the ones that are

 

[Ricyteach]

This thread is high on the Google search results for "excel named range multiple worksheets", so I thought I'd add a better solution. Set the Named Range equal to the following (with scope set to Workbook):

=![COLOR=#333333]$C$2:$C$500[/COLOR]

Note the exclamation point.

The Named Range will now refer to the local value of $C$2:$C$500 on each worksheet.

 

[Man_Over_Game]

This thread is high on the Google search results for "excel named range multiple worksheets", so I thought I'd add a better solution. Set the Named Range equal to the following (with scope set to Workbook):

=![COLOR=#333333]$C$2:$C$500[/COLOR]

Note the exclamation point.

The Named Range will now refer to the local value of $C$2:$C$500 on each worksheet.

I made an account just to comment on how perfect and awesome your solution is after researching into it myself, and to give you as many updoots as my new account can afford.

 

[Rantunes]

This thread is high on the Google search results for "excel named range multiple worksheets", so I thought I'd add a better solution. Set the Named Range equal to the following (with scope set to Workbook):

=![COLOR=#333333]$C$2:$C$500[/COLOR]

Note the exclamation point.

The Named Range will now refer to the local value of $C$2:$C$500 on each worksheet.

I apologize for unearthing an old thread but I'm having trouble making this solution work.

I'm looking to have the range be only one cell in each sheet. When trying to adjust the range I get an error saying that there's something wrong with the formula. I've also tried changeing the COLOR name to my native language, COR, but it didn't work.
I'm also not sure what to color #333333. The value? The cell background? The sheet color?

Can someone please explain the formula to me?