Naming Fromulas in Excel

[ashtonp2]

Hi
Can anyone help me. I have created some formulas in Excel which need to be on approx 9,000 rows. Someone told me if i named the formulas the spreadsheet would be smaller and work faster. I have the original formula on the top row and then copied it to the paste board, gone to the insert, name and define. I have then pasted the formula in the bottom box (refers to) and given it a name. This seems to work and has made the spreadsheet smaller. However some of the formulas are to long in length when I try this way. Is there a another way I can to name formulas or make a cell equal to the same formula in previous cell, which may make the spreadsheet worker faster

Thanks

Paul

 

[DPlank]

What does the formula do?

What is the formula?

It will help us to optimise it if we know what we are optimising.

Thanks
Dave

"Probably the only Test Analyst on Tek-Tips"

Take a look at Forum1393 & sign up if you'd like

 

[ashtonp2]

Hi the formula is
=CONCATENATE(IF('Look up tables'!$AI$3="all","All",H4),IF('Look up tables'!$AI$5="all","All",I4),IF('Look up tables'!$AI$6="all","All",G4),IF('Look up tables'!$AI$7="all","All",E4),IF('Look up tables'!$AI$8="all","All",F4),CONCATENATE(C4,D4,B4),IF('Look up tables'!$AI$12="all","All",L4),N4)

Basically it is creating a unqiue refernce for me to use in a sume if. It concatenate's cells however depending on the results of a drop down menu then it will either use results of a certain cell or use the word all.

Thanks

Paul

 

[Crystalyzer]

Paul,

You could name all the if statement formulas and then using those names you could name the concatenate formulas.



Thanks and best regards,
-Lloyd