Using Sheet Name in combination with a named range [VBA/365/MAC]

[LeighG]

Hi,

Office 365 / Mac OS

I have the following code which works perfectly. However I'd like to combine the Sheet Name (from the current workbook) in combination with the Named Range. I can't seem to get the syntax right.

 Cells(1, 1).Value = ThisWorkbook.Names("Balance").RefersToRange(1, 1)

Thanks!

 

[LeighG]

To scope a name to the sheet level on a Mac, one the DefineName dialog enter the name qualified by sheet reference in the Name box

e.g. Sheet2!MyName will create a name MyName that is only valid on Sheet2.

Great thanks, that's helpful.
And so if a sheet with a named range as per your example above is copied, does the new sheet have a range with the same name that is only scoped to that new sheet.
So in your example, If I copied Sheet2, and renamed it Sheet3, is there now a named range Sheet3!MyName scoped only to sheet 3.
(I think I've tested that this is the case, but validation would be nice)

 

[mikerickson]

That's what happens to me.

 

[mikerickson]

Have you looked at relative sheet referencing in names

If you define a name, scoped to the workbook, MyName, RefersTo =!$A$1
(note the !)

the formula =MyName will return the value from A1 of the sheet on which the formula is entered.