Named range over multiple worksheets?

BLaCKouT

New Member
Joined
May 31, 2009
Messages
42
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:
Code:
=Sheet1!$C$2:$C$500
and apply the following conditional formatting to cells within the range:
Code:
=(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? :confused:

Many thanks in advance,
B.
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
    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

New Member
Joined
May 31, 2009
Messages
42
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 :LOL:

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

Banned
Joined
Feb 8, 2003
Messages
5,378
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

New Member
Joined
May 31, 2009
Messages
42
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

New Member
Joined
Jun 19, 2013
Messages
2
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):

Code:
=![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.
 
Last edited:

Man_Over_Game

New Member
Joined
Aug 30, 2019
Messages
1
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):

Code:
=![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.
 

Forum statistics

Threads
1,082,323
Messages
5,364,579
Members
400,809
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top