0

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?

1
  • Google Docs it's a word processor app, it hasn't a Data menu, the app that has it is Google Sheets, a spreadsheet app.
    – Rubén
    Jul 30 at 18:20

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.