Create a worksheet-specific named range without VBA

lovallee

Board Regular
Joined
Jul 8, 2002
Messages
216
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):

Code:
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

mtb'r

New Member
Joined
Sep 8, 2006
Messages
42
Do you need to have the offset range NAMED? If not, you could use this:

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

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

lovallee

Board Regular
Joined
Jul 8, 2002
Messages
216
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Board Regular
Joined
Jul 8, 2002
Messages
216

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Board Regular
Joined
Jul 8, 2002
Messages
216
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,136,354
Messages
5,675,303
Members
419,560
Latest member
g3org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top