In a text about Excel I have read the following:
=SUM(!B1:!K1) when defining a name for a cell and this was entered into the Refers To field.
What does this mean?
2 Answers
When entered as the reference of a Named range, it refers to range on the sheet the named range is used on.
For example, create a named range MyName refering to =SUM(!B1:!K1)
Place a formula on Sheet1 =MyName. This will sum Sheet1!B1:K1
Now place the same formula (=MyName) on Sheet2. That formula will sum Sheet2!B1:K1
Note: (as pnuts commented) this and the regular SheetName!B1:K1 format are relative, so reference different cells as the =MyName formula is entered into different cells.
If you use that forumla in the name manager you are creating a dynamic range which uses "this sheet" in place of a specific sheet.
As Jerry says, Sheet1!A1 refers to cell A1 on Sheet1. If you create a named range and omit the Sheet1 part you will reference cell A1 on the currently active sheet. (omitting the sheet reference and using it in a cell formula will error).
edit: my bad, I was using $A$1 which will lock it to the A1 cell as above, thanks pnuts :p
1
