Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 480
Gergely Avatar asked Nov 20 '14 10:11

Gergely


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.

like image 126
chris neilsen Avatar answered Nov 11 '22 13:11

chris neilsen


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

like image 31
Lefty Avatar answered Nov 11 '22 15:11

Lefty