Named Formula?

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,589
Office Version
  1. 365
Platform
  1. Windows
You can enter a valid formula in the named range 'refers to' box.
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
36,692
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Dec 30, 2008
Messages
12,589
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,980
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,589
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,265
Messages
5,635,158
Members
416,844
Latest member
ryanangus496

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top