Minimizing formulas, re-calculating by a given range and time

**sphinx_ps** · 03-03-2010, 06:06 PM

Hi,

The first question:
Is there some way to replace a big formula to small?

I have, for example, =AVERAGE(MAX(MIN(CONCATENATE("SH1!",N5))))
and I want to use it many times:
=NEW_NAME("SH1!",N5)
=NEW_NAME("SH1!",K9)
and so on

Does Excel have some options to make this, without vba of course.

The second question:
Is there a way to trigger the formula at particular time without using vba.

The third question:
How can I re-calculate the formula when something is changing in a given range, for example:
I have =CONCATENATE("SH1!",H7), and I want re-calculate it when something is changing in J8:J88.
Is it possible?

**rwgrietveld** · 03-03-2010, 06:27 PM

All your question are a bit strange to me.

It is thepower of excel to have a dependancey scheme how to calculate a Workbook. It is called decision models and one can read all about it here.
http://www.decisionmodels.com/calcsecretsh.htm

If you want to by-pass that VBA is required.

Another way is to set calculation to manual and by pressing F9 you can do a re-calculate (CTRL/ALT/F9 – Full Calculation).

About your first question. Why?! to start with, but if you want to you can (as you don't want to you VBA to create a User Defined Function UDF) use the Name to store part of your formula.

About the third question. If H7 is dependant on 1 or more cells in J8:J88 than it will be done automatically. Also here it can be triggered using VBA (Events).

Just a note. The Concatenate can be replaced by ="SH1!"&H7, where H7 most probably will contain a cell reference.

Hope this helps. Maybe show us the big picture / your intentions.