POWER FORMULA TECHNIQUES

Created by David Hager

The problem – to make a 3D formula that adjusts in a z-relative manner

when it is filled across worksheets. Two separate solutions to this problem

that use a similar methodology are shown below.

Making a 3D Running Total –

This example uses information entered in column A, with the 3D Running Total

formula in column B.

Define shtPos as:

This formula returns the sheet position of the active sheet as an integer.

=GET.DOCUMENT(87)

Define wsNames as:

This formula returns an array of sheet names in the active workbook.

=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND(“]”,GET.WORKBOOK(1)))

Define shtName as:

This formula returns the active sheet name.

=INDEX(wsNames,shtPos)

Note: The string returned from GET.DOCUMENT(76) could have been modified

to obtain the active sheet name, but the method shown above was used since

the modified array of sheet names was available (and needed for another

3D example).

Define RunningTotal as:

=EVALUATE(“SUM(Sheet1:”&shtName&”!A”&ROW()&”)”)+NOW()*0

This formula returns the sum for the cell in column A for each worksheet

from Sheet1 to the worksheet where the formula resides. For example, if

the active sheet was Sheet4 and this formula was in B2, this formula

(entered as =RunningTotal) would be the equivalent of the Excel formula

=SUM(Sheet1:Sheet4!A2). Since this formula incorporates xlm macro functions,

it must be forced to recalculate. This is done by using the term NOW()*0,

since NOW() is a volatile function.

Making a 3D Moving Average –

Define shtNamem2 as:

=INDEX(wsNames,shtPos-2)

This formula returns the sheet name of the worksheet 2 tabs to the left of

the active sheet.

Define MovingAverage as:

=EVALUATE(“AVERAGE(“&shtNamem2&”:”&shtName&”!A”&ROW()&”)”)+NOW()*0

This formula returns the average of the values in the sheet 2 tabs to the

left of the active sheet to the active sheet. For example, if the active

sheet was Sheet4 and this formula (entered as =MovingAverage) was in B2,

this formula would be the equivalent of the Excel formula =AVERAGE(Sheet2

:Sheet4!A2).

**Editor’s Note:** Here’s the first method in action:

Same setup, but for the second method:

Using an excel4 macro command in a defined name can be problematic. If you put it in a formula in a cell, then copy the cell to another sheet, it causes a general protection fault in xl2000 and earlier. Just a heads up.

Yeha Tom you’re right ,i got tired of trying it and then came back here to see if i have missed something.

Thanks Kusleika…worked for me..Well both methods work fine for me…