Create a worksheet-specific named range without VBA

[lovallee]

Hello,

I like to create the CellAbove named range.
This is a cell-relative named range that refers to the "cell above" and can be used in formula such as sum(A1:CellAbove).

The problem: for a cell-relative named range to work, it needs to be worksheet-specific (just like the Print_Zone name, which is worksheet-specific i.e. works only for the worksheet it's been added to) .

The only way I found to create a worksheet-specific name is using VBA (this particular code add CellAbove to each sheet in the workbook):

Sub AddCellAbove()

Dim Sh As Worksheet

For Each Sh In Worksheets
Sh.Names.Add Name:="CellAbove", RefersToR1C1:="='" & Sh.Name & "'!" & "R[-1]C"
Next

End Sub

Question: is it possible to create a worksheet-specific named range without using VBA ?

Thanks!

Louis

 

[mtb'r]

Do you need to have the offset range NAMED? If not, you could use this:

=SUM(A1:OFFSET(A9,-1,0))

In this example, the equation is entered into cell A9.

 

[lovallee]

Thanks mtb'r

I like the your solution, altough it use the volatile function (offset).

I think I still prefer the "relative R(-1)C named range" solution as it makes formula cleaner and easier to read. It also avoid using a volatile function. The inconvenient seems to remain the one needs VBA to set it up.


Louis

 

[pgc01]

Hi Louis

Try the following:

Select one cell not in the first row. I'll use C3

Insert>Name>Define

Enter CellAbove in the name

In the Refers To box, excel proposes something like

=Sheet1!$C$3

Change it to

=Sheet1!C2

This should do it. Since you used a relative address you can use CellAbove in any cell.

Please test it.

Hope this helps
PGC

 

[lovallee]

Thanks PGC,

The problem with the
CellAbove=Sheet1!C2
approch is that it will only work fine in Sheet1.

If you input CellAbove in Sheet2!C3, CellAbove will return Sheet1!C2.

That is whay I say that a relative name should be worksheet-specific.

Louis

 

[pgc01]

Hi Louis

I understood exactly the opposite.

Anyway, if that's what you want, remove the worsheet name.

In the example I gave in my last post

Change it to

=!C2

Hope now it's what you want.
PGC

 

[lovallee]

Thanks PGC,

NamedRange CellAbove=!C2 works...partially!

But there is a very weird drawback... CellAbove does not recalculate automatically when the value in the "cell above" changes.

Example:
C2 constant = 5
C3 formula: = CellAbove, and returns 5. Ok.

C2 constant changes for 6
C3 formula: = CellAbove, and still returns 5 until C3 is edited and reconfirmed by pressing enter.

Excel has some difficulties with the "=!C2" syntax and does not recalculate properly...

 

[pgc01]

Hi Louis

I have excel2000, I had tested it yesterday but retested just now and it works perfectly in my xl.

What you are describing is what happens when you have Tools>Options>Calculation>Manual, but I suppose that is not the case.

Anyway I'm sorry it doesn't work for you and and if you check the code you posted you'll see that this is what you are doing with vba. I wish I knew why it does not work for you.

Kind regards
PGC