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

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
  • 4,015
-1

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
  • 9,403