J-Walk & Associates, Inc.

Blogs As Range References

When column references go up to XFD, you can spell things in formulas. Here are a few Excel blogs, spelled out in valid Excel 2007 formulas:

=SUM(DAI:LYD,OS:EOF,EX:CEL)

=SUM(CON:TEX,TU:RES,BL:OG)

=SUM(BA:CON,BI:TS)

=SUM(NEW:TON,EX:CEL,BA:CH)

=SUM(PT:SB,L:OG)

=SUM(S:PR,E:AD,SH:EET,P:AGE,BL:OG)

Challenge: Write a macro that accepts a text string, and creates formulas like these automatically.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Debra Dalgleish. Comment posted 24 January, 2010 10:47am

   More fun than a secret decoder ring! I'd work on a macro, but I have to write a post for =SUM(CON:TEX,TU:RES,BL:OG).
2. By John Walkenbach. Comment posted 24 January, 2010 12:35pm

   =SUM(DEB:RAH,DAL:GLE,I:SH,ISB:USY,RI:GHT,N:OW)
3. By Chandoo. Comment posted 25 January, 2010 3:53am

   and you forgot... SUM(POI:NTY,HAI:RED,D:IL,BE:RT)
   
   interesting challenge... I will give it a try this friday
4. By Rick Rothstein (MVP - Excel). Comment posted 28 January, 2010 12:43pm

   Your first range (POI:NTY) would not be valid in Excel... well, it won't raise an error, but Excel will flip it around to NTY:POI inside the SUM function and, thus, destroy the display of the word "POINTY".
5. By Marilyn Varley. Comment posted 13 April, 2010 8:34am

   You have WAY TOO MUCH fun at your job!!!!! But that's one of the reasons I adore you. The biggest reason is that you are THE BEST technical writer I have encountered, EVER [including myself]! Your book [Power Programming with VBA 2007] gave me the courage to dive into programming for my XL spreadsheets. I have challenged myself to automate via a "wizard" several of the activities that I perform on a daily basis. I have made some progress [35%] but I think some of the simpler things are escaping me. I'm going to look at your "for Dummies" version tonight.