Excel 2010: Create named range without a sheet reference in the formula

Question

In Name Manager I created a named range with this reference:

=COUNTIF($A$4:A4, Tbl_InventoryMain[Barcode])

Excel automatically inserts the current sheet name prior to the $A$4:A4. I need this to just stay $A$4:A4 in order to use this same reference on other sheets. I don't want to create a named range for each sheet.

How can I get this named reference to work on any sheet?

This is only a piece of a long formula. This particular equation is repeated in an array. I'm trying to reduce the size of the formula and speed up the calculations by placing the parts of the overall formula in name manager.

Answer 1

My answer focuses on creating a reference to $A$4:A4 that works on all worksheets since that's the part of the reference to which Excel is prepending the unwanted sheet name. (I assume you're not having any trouble referring to the table.)

You can solve this problem using the INDIRECT function as follows:

1. On any worksheet, select cell A1

2. Create a named range with a reference of:

    =INDIRECT("$A$4"):INDIRECT("R[3]C[0]",FALSE)
   

Per your question, the second cell in your reference of $A$4:A4 is relative and this name's reference is built accordingly. So if you use this name in cell A1 it will return the range $A$4:A4. If you reference the name from B1 the name will correctly return a range of $A$4:B4.

How it Works

The first half of the formula is simple. INDIRECT("$A$4") will always refer to cell A4 on the current worksheet.

The second half is a bit more complicated. INDIRECT("R[3]C[0]",FALSE) refers to "3 rows down in the same column, relative to the current cell". If you're in cell A1 this is cell A4. The FALSE argument tells the INDIRECT function to interpret the text as R1C1-style reference instead of the default A1-style reference.

The Completed Formula

Your final name reference will look something like this:

=COUNTIF(INDIRECT("$A$4"):INDIRECT("R[3]C[0]",FALSE), Tbl_InventoryMain[Barcode])