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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,496
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,534
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,496
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,976
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,496
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,128,110
Messages
5,628,761
Members
416,337
Latest member
tl3phd

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