3
1
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.
Won't the "indirects" slow the workbook down as a volatile function? – mechengr02 – 2015-10-27T17:13:44.940
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 – 2015-10-27T18:38:01.733
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 – 2018-05-09T05:39:53.957
That's a great question! You should post it as a new question, referencing this post for context if necessary. – I say Reinstate Monica – 2018-05-09T10:40:33.667