How to make a variable relative to the sheet it is in?

Question

In Google Sheets, I can define a variable such as interestRate by using Data -> Named Ranges.

Then I can use the variable interestRate as if it is a number, like 10%.

But what if in Sheet2, I want to experiment with a different interest rate, such as 7%, and want to just go to Sheet 1, Select All, Copy, and Paste it to Sheet 2?

In that case, interestRate still refers to the number in Sheet 1, but I want it to be relative to current sheet's cell B1, for example, not cell B1 in Sheet 1.

How can it be done?

Answer 1

What you are asking for is a 'sheet-level named range', which is possible although the mechanism of creating it is not obvious, and it can't be done the way you have tried above:

1. Create your named range interestRate in B1 of a sheet (lets call this sheet 'Original' for simplicity of explanation)
2. Right-click on the sheet name tab for this sheet at the bottom of the screen and select 'Duplicate'
3. A duplicate of the original sheet is made (called 'Copy of Original') and displayed. Open up the 'Named ranges' sidebar. You will see that the original named range interestRate is there (pointing at Original!B1), but there is also a new entry 'Copy of Original'!interestRate pointing at 'Copy of Original'!B1

You can rename the copy sheet to suit your needs and the 'copy' named range will change to match. To use the 'copy' named range in a formula you must use the sheet name prior to the named range; the named range on its own always refers to the original sheet.