Microsoft Office Tutorials and References

In Depth Information

**USE A SELF-REFERENCING FORMULA**

Part

I

Figure 58.
VLOOKNEW
returns a value that appears to the let of the key i eld

Summary:
User-deﬁ ned functions can provide improvements on the
VLOOKU
P

function.

Source:
http://www.mrexcel.com/forum/showthread.php?t=112275

The post was nominated by Matt Hohbein. Thanks to Zack Barresse and Peter

Moran.

USE A SELF-REFERENCING FORMULA

Challenge:
Shades was looking for a formula to reverse letters in a cell. This can

easily be accomplished using a VBA function. However, Shades had challenged

people to write a formula. A new member, Hady, came along with this solution.

Gotcha:
The technique in this topic is not compatible with the Evaluate Formula

feature. If you use Evaluate Formula on a self-referencing formula, you run the

risk of crashing Excel and losing your work.

Solution:
To solve this problem, you use a self-referencing formula. Follow

these steps:

Select Tools, Options, Calculation. Choose Iteration and set the maximum

iterations to 100.

Enter any sentence in A1.

In cell B1, enter this formula and press Enter:
=IF(LEN(B1)<LEN(A1)+1

,B1&MID(A1,LEN(A1)+1-LEN(B1),1),IF(MID(B1,1,1)<>"0",B1,R

IGHT(B1,LEN(A1))&" "))
, When you press Enter, you get a result of
0

and the last character from cell A1. This is normal.

Press F9 again, and you get
0
, the last character, and the second-to-last

character.

Press F9 again, and you get
0
and the last three characters in reverse.

1.

2.

3.

4.

5.