Large formulas possible in Named Range ?

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
195
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
 

Some videos you may like

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
Joined
Feb 16, 2002
Messages
8,458
Why don't you post some the formulae you use and maybe someone can come up with more efficient ones.
 

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
195
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,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,686
Members
412,481
Latest member
nhantam
Top