Large formulas possible in Named Range ?

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199
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
Joined
Feb 16, 2002
Messages
8,459
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
199
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,"")
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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