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-defi 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.
 
 
Search JabSto ::




Custom Search