Name global references

[Nykyta]

1. I have sheets: AS1, AS2 and AS3
2. Created name: "=A1 - B1"
I want that name to work everywhere but seems that is impossible. Name manager added sheet name to formula as "='AS1'!A1 - 'AS1'!B1". Now, it works everywhere but it always referers to AS1 sheet only and I didn't want that.

Is it possible to create global name which will work everywhere (
name formula to be "=A1 - B1" instead of
"='AS1'!A1 - 'AS1'!B1"
)?

Thanks in advance.

 

[Jerry Sullivan]

Hi Nykyta,

I don't think that can be done with a single Name that is defined with Workbook scope.

As an alternative you could define the Name with Worksheet scope then copy a cell with a formula that references that Name to other sheets.
This will create similar Worksheet level Names on each sheet.

One other workaround would be to create a VBA User Defined Function (UDF). That function could be used anywhere in your workbook to return the result of your relative range formula.

 

[mikerickson]

You can use a formulation like this.

Name: myValue
RefersTo: =!$A$1-!$B$1

The ! creates a relative reference to the sheet.

You also want to be careful of the row/column relative/absolute addressing, which does carry over to names.

If you select a cell in row 1 and define
Name: AplusB
RefersTo: = !$A1+!$B1

and then put =AplusB in any cell, it will return the sum of the column A and B values for that row.

 

[Jerry Sullivan]

Mike, I hadn't seen the ! used to create a relative reference to the sheet. That's great to know. Thanks.

 

[mikerickson]

Names defined like that interact poorly when referenced in VBA routines.

 

[Jerry Sullivan]

Names defined like that interact poorly when referenced in VBA routines.

Are you referring to Names with relative (column/row) references in general or specifically ones that use "!" to make them relative to the sheet?

 

[Nykyta]

Jerry, thank you for your time and answer. Basically, I am using relative references in names most of my time. They are great for everything like:

name     val_1     val_2     SomeKindOfResult
----     -----     -----     ---------
mike     25          24      =SubTotal
imek     25          24      =SubTotal
kemi     25          23      =SubTotal
----     -----     -----     ---------
         =Total    =Total

When you have many sheets with 10-12 names per each… it’s not easy to maintain all of that. I think that Mike didn’t mean how do you refer your cells (by using [r][c] or AB) but in relative references in general (by using AB instead of $A$B).

Now, thank you Mike.

You can use a formulation like this.

Name: myValue
RefersTo: =!$A$1-!$B$1

You are genius! Very grateful for your lesson. And I am not novice at all.

Everything works so far: and =!A2-!B2 and =!$A2-!$B2… I just need to check this behavior (technique, trick?) in more complex UDFs… but probably, everything will be ok.

Thank you very much again!! Do you have personal web-page / site?

 

[mikerickson]

Are you referring to Names with relative (column/row) references in general or specifically ones that use "!" to make them relative to the sheet?

Both, its curious that if aName has relative row or column references that
Range("aName") returns a different cell than ThisWorkbook.Names("aName").RefersToRange

Sub test()

    With Sheets("Sheet2")
        .Activate
        .Range("B12").Select
    End With
    ThisWorkbook.Names.Add Name:="oneName", RefersToR1C1:="=Sheet2!R[1]C[1]"
    
    MsgBox "N1s2Rg=" & Range("oneName").Address(, , , True) ' Sheee2!$B$2
    MsgBox "N1s2Nm=" & ThisWorkbook.Names("oneName").RefersToRange.Address(, , , True) 'Sheet2.$C$13
    
    Sheets("Sheet1").Activate: Sheets("Sheet1").Range("Z100").Select
    
    MsgBox "N1s1Rg=" & Range("oneName").Address(, , , True) ' Sheet2!$B$2
    MsgBox "N1s1Nm=" & ThisWorkbook.Names("oneName").RefersToRange.Address(, , , True) 'Sheet2.$AA$101
    
    Rem relative sheet, row and column
    With Sheets("Sheet2")
        .Activate
        .Range("B12").Select
    End With
    ThisWorkbook.Names.Add Name:="twoName", RefersToR1C1:="=!R[1]C[1]"
    
    MsgBox "N2s2Rg=" & Range("twoName").Address(, , , True) 'Sheet2!$B$2
    'MsgBox ThisWorkbook.Names("twoName").RefersToRange.Address(, , , True) ' errors
    
    Sheets("Sheet1").Activate: Sheets("Sheet1").Range("Z100").Select
    
    MsgBox "N2s1Rg=" & Range("twoName").Address(, , , True) 'Sheet1!$B$2
    'MsgBox ThisWorkbook.Names("twoName").RefersToRange.Address(, , , True)
    
End Sub

It appears that if a named range uses relative row or column reference Range("aName") returns that reference relative to A1 while Names.RefersToRange returns it relative to the ActiveCell.

If a name uses relative sheet reference, Range("aName") behaves as with absolute sheet reference, but Name.RefersToRange errors.

 

[Jerry Sullivan]

That's interesting. It's surprising that Workbook.Names.RefersTo and Range("aName") return different range references.

Great examples. Thank you.