# Large formulas possible in Named Range ?

#### StanSz

##### Board Regular
I've got a large ss (5000 rows) with many complicated formulas.

Since the wb has gotten quite large I've started thinking about ways to make the wb more efficient.

I currently have these large formulas copied throughout 5000 rows.

Is the following thinking worthwhile, and possible:

I thought about copying a large formula into a Name, "Formula1" for example. (Insert/Name/Define)

Then, rather than copying my complicated formula throughout 5000 rows, I would simply enter =Formula1 in the cell in question and copy this =Formula1 throughout the 5000 rows.

My thinking was that the complicated formula would only be entered once, somewhere in Excel's memory, and then the much simpler "Formula1" would be what would be entered in each cell.

Does this save 'space' or memory ??

Is this a worthwhile approach ??

In testing this "Formula1" idea I find that it does work, although there seems to be a limit to the amount of a formula that I can copy (or enter) into the Defining section of the Named Range. And this of course means that the largest and most complicated formulas don't seem to be able to be entered into a Name.

Is there a limit to the amout of characters that can be entered into a Named formula ??

Any other thoughts on how to save 'space' or memory ??

Thanks

StanSz

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Brian from Maui

##### MrExcel MVP
Why don't you post some the formulae you use and maybe someone can come up with more efficient ones.

#### StanSz

##### Board Regular
Brian,

OK, here's a sample formula, although I still would like to get some info on the limitations, and advantages of entering formulas in Names.

=IF(AND(BI31=1,INDEX(K:K,AB31)<>1,J31<60,'Prices'!J31<>1,
NOT(OR(INDEX(BA:BA,AB31)=1,INDEX(BD:BD,AB31)=1,INDEX(BE:BE,AB31)=1,
INDEX(DH:DH,AB31)=1,AND(INDEX(BF:BF,AB31)=1,INDEX(J:J,AB31)<60))),
NOT(AND(AND(J33<59,J32>59,J31<59,K31>0.3,NOT(AND(AJ31=1,AN31=1))),D31<>E31)),
NOT(AND(INDEX(BB:BB,AB31)=1,K31*100<J31))
NOT(AND(OR(INDEX(BC:BC,AB31)=1,D31<>E31),B31<B36)),
NOT(AND(INDEX(CN:CN,AB31)=1,J31<J32,D31<>E31)),
OR(J31<J32,AND(D31="L",D32="S"),AND(E31="L",E32="S"))),1,"")

#### Brian from Maui

##### MrExcel MVP

Replies
5
Views
319
Replies
3
Views
248
Replies
2
Views
1K
Replies
1
Views
490
Replies
1
Views
572

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,806
Messages
5,833,778
Members
430,232
Latest member
Testsubject

### 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