0

With reference to the post, Excel 2010: Create named range without a sheet reference in the formula, I would like to know can it be done using non-volatile function because I have 200 sheets to apply such type of formula.

Description of the issue: In Name Manager I created a named range with this reference =sum($O$1:$O$15), Excel automatically inserts the current sheet name prior to the $O$1:$O$15. I need this to just stay $O$1:$O$15 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 without using volatile function, such as indirect method?

I tried using index function like =sum(index($A:$XFD,1,15):index($A:$XFD,100,15)) but the excel automatically change the formula as =SUM(INDEX('sheet'!$1:$1048576,1,15):INDEX('sheet'!$1:$1048576,100,15))

Any brilliant idea please. Thank you in advance.

8平民
  • 29
  • 6
  • I don't know that this is possible in current versions of Excel. Perhaps you could use a VBA macro instead to accomplish your computational task? – Ron Rosenfeld Jul 22 '23 at 13:01
  • VBA is not an option either because UDF can create another problems, e.g. sometimes the UDF does not update itself unless the UDF is set to volatile. – 8平民 Jul 22 '23 at 13:07
  • That's why I suggested a macro (sub) and not a UDF. It all depends on the nature of your task and under what circumstances you need to have things recalculated. Fpr example, you could generate the SUM of a range on each of 200 sheets in a fraction of a second if you write a macro properly. – Ron Rosenfeld Jul 22 '23 at 13:39
  • Thank you for your suggestion. But I think this is the last resort. Because the actual situation needs functions in functions and more than 10 function-in-function for each sheet. It would make it easier to simply use formula to get it done. – 8平民 Jul 22 '23 at 19:15

2 Answers2

2

You can create names in the Name Manager using notation like

rng : =!A1:A20
Only an exclamation mark before the address!
Such a name refers to the current sheet, so you can use it in any sheet in the workbook, but you cannot use it externally (from another sheet or workbook).
Notation like this may be used only in defined names and not in formulas in cells. In cells you simply use addresses without qualifier.

MGonet
  • 151
  • 4
  • You are awesome. That definitely do the trick. But I am wondering if it is non-volatile way. I will just assume it is non-volatile and accept it as an answer first. Thank you for your brilliant idea. – 8平民 Aug 24 '23 at 18:33
0

This issue can be solved using this simple method:

  • Use this formula =INDIRECT("A1:A20") .
  • Suppose the given Name is ALLRANGE.
  • You may construct further formula like this =Sum(ALLRANGE).

- Remember while using Name Manager, the SCOPE for the Named Range must be WORKBOOK.

- The Name ALLRANGE will always refer A1:A20, wherever will be used.

N.B : Adjust cell refences for the formula as needed.

Rajesh Sinha
  • 8,995
  • 6
  • 15
  • 35
  • 1
    But he asked specifically for a solution with non-volatile functions. `INDIRECT` is volatile. – Ron Rosenfeld Jul 22 '23 at 10:15
  • Thanks for clarification, Ron. Indirect method can slow down the workbook significantly. If I used indirect method then everytime I changed a cell value, it need calculation of 1-2 seconds for each change T^T. – 8平民 Jul 22 '23 at 11:03
  • @RonRosenfeld ,,, even though principally INDIRECT is a VOLATILE function ,,,, but while using with Named Range is working as Non-Volatile ,,, I've tested with data before I've posted this ,,, as requested by the OP ☺ – Rajesh Sinha Jul 23 '23 at 05:43
  • Please publish your test procedure and results, as this runs contrary to most of what has been written. – Ron Rosenfeld Jul 23 '23 at 10:50
  • @RonRosenfeld ,,,, to test I've tried F9, also inserted new value in cells,, sheet Rename, Auto Filter,,, but in neither situations I found any change in the cell,,, formula is written ,,, I don't think here I can upload Video ,, otherwise is the most easiest medium to justify . – Rajesh Sinha Jul 23 '23 at 12:21
  • I am assuming from your comment that you have **not** published this testing anywhere, and your comment does not provide sufficient information to easily reproduce it. In my experience, a video is much less useful than writing for this kind of thing. My testing on the OP's putative environment, with 200 sheets, shows that execution time using `INDIRECT` in the named formula slows things down significantly (about three-fold). – Ron Rosenfeld Jul 23 '23 at 17:39
  • @RonRosenfeld ,,, yes U are write on the Huge data set few of the commands pull downs the processing time,,, but OP's concern was a 3D formula ,,, without Sheet references,,,, so I found the INDIRECT ,,,, otherwise INDEX & COUNTA can b used also,, but it ultimately clips the Sheet Name,,,, I 'm waiting soon somebody will post another one ☺ – Rajesh Sinha Jul 24 '23 at 05:37