# 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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### 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,"")

Replies
3
Views
87
Replies
3
Views
231
Replies
6
Views
265
Replies
2
Views
72
Replies
6
Views
362