CODEWITHSUNDEEP
excelexcel-formula
Gergely
Gergely

Reputation: 7525

What does an exclamation mark before a cell reference mean?

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?

Upvotes: 19

Views: 164774

Answers (2)

Lefty
Lefty

Reputation: 426

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

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53126

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.

Upvotes: 13

Related Questions