4

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.

Improve this question

1 Answer 1

Reset to default
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])
Improve this answer
4
  • Won't the "indirects" slow the workbook down as a volatile function?
    – mechengr02
    Oct 27, 2015 at 17:13
  • That depends on a number of things, such as how large/complex the Excel workbook is, how fast your CPU is, etc. Because there's no one-size-fits-all answer to that question, its best to try this solution and see if it works in your environment. If not, you may need to forego using Named Ranges for this particular application.
    – I say Reinstate Monica
    Oct 27, 2015 at 18:38
  • Is it possible to use this dynamic syntax in a chart? I can get the formula to work correctly, but as soon as I try and put it in a chart it breaks. e.g. =test.xlsm!C_Visits
    – Chris
    May 9, 2018 at 5:39
  • That's a great question! You should post it as a new question, referencing this post for context if necessary.
    – I say Reinstate Monica
    May 9, 2018 at 10:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .