# Named Formula?

#### smitpau

##### Board Regular
Hi,

Firstly I don't believe this is possible but thought I'd check.

Is there a way to name a formula, so like how a named range works but for formulas so say "INDEX 1" could refer to a long index formula to shorten the characters in the formula box.

Thanks,

Paul

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### jasonb75

##### Well-known Member
You can enter a valid formula in the named range 'refers to' box.

#### smitpau

##### Board Regular
Thanks that's useful but that's not draggable in the sense that I believe it will only return the same value rather than having a dynamic cell reference.

#### RoryA

##### MrExcel MVP, Moderator
If you use relative references in the formula, they will adjust when used in different cells. Note that the references will be relative to the cell that is active when you define the name.

#### jasonb75

##### Well-known Member

The exact requirement is open to interpretation in a number of ways, if I'm following what is being asked correctly then something like this might work

=INDEX(INDEX_1,ROWS(A\$2:A2))

#### smitpau

##### Board Regular
Thanks for the suggestions but don't think I was clear enough, in the example below, is there a way to name the formula in cell D12, such that the named formula could be dragged and effectively perform the same indexing of the above table (D6:G9)?

I did try the name within an INDEX method but it didn't seem to work.

Book3
CDEFG
51234
6110203040
7210203040
8310203040
9410203040
10
11
1210203040
1310203040
1410203040
1510203040
Sheet1
Cell Formulas
RangeFormula
D12:G15D12=INDEX(\$D\$6:\$G\$9,\$C6,D\$5)

#### mikerickson

##### MrExcel MVP
The key is to have the correct cell active when you enter the Name with relative references.

Select cell D12
Define a name MyName RefersTo: =INDEX(\$D\$6:\$G\$9, \$C6 , D\$5)

When you enter =myName in D12, it will look to \$C6 and D\$5 for data.
When you enter (or drag to) that formula in D13, it will look to \$C7 and D\$5

Which cell is active when you enter a named formula with relative references is very important.

#### jasonb75

##### Well-known Member
Could you give an example formula of an actual formula that you want to do this with?
For the example that you have given, you're over complicating things by trying to simplify them. With a longer formula there might be ways to simplify it other than what you're trying.

Replies
0
Views
82
Replies
8
Views
128
Replies
5
Views
29
Replies
2
Views
175
Replies
2
Views
48

1,127,555
Messages
5,625,490
Members
416,111
Latest member
User81

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back