Named Formula?

[smitpau]

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

 

[jasonb75]

You can enter a valid formula in the named range 'refers to' box.

 

[smitpau]

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]

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]

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]

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
	C	D	E	F	G
5		1	2	3	4
6	1	10	20	30	40
7	2	10	20	30	40
8	3	10	20	30	40
9	4	10	20	30	40
10
11
12		10	20	30	40
13		10	20	30	40
14		10	20	30	40
15		10	20	30	40
Sheet1

Cell Formulas
Range	Formula
D12:G15	D12	=INDEX($D$6:$G$9,$C6,D$5)

 

[mikerickson]

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

In your case above:
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]

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.