tricks of gurus

[PA HS Teacher]

Here is one from Dave Hawley that I found today looking around for something else. You can take a screen shot of just the selected area of a worksheet by selecting what you want a shot of, holding down the Shift key, select the edit menu then Copy Picture.

Good find Oorang. That's much nicer than resorting to print screen like I normally do.

 

[hatman]

How about Offset()? The first time I found that function through the Equation Builder Wizard, I said to myself "What the heck is THAT good for?" The help file is nebulous in terms of how it could possibly be useful. When I finally stumbled upon it's use in Dynamic Named Ranges, I nearly had an apoplexy. Now I can't imagine building a robust application without it.

And don't forget the use of Match() and Index() in lieu of Vlookup()... invaluable, but these two functions are treated separately in the help files without a single mention that they can be used in conjunction with very powerful results.

 

[litrelord]

Perhaps with the standard warning that OFFSET is volatile etc etc

Very useful all the same though.

 

[erik.van.geit]

this may not be a real "trick" but what looks to a lot of us as a "trick" isn't more then some knowledge gathered through the means available to all of us

so
one of the main tricks of gurus is that they
use the HELPfiles
and are able to find their way on the internet to get what they need

 

[hatman]

Of course, knowing when the downside of volatility is outweighed by the benefits (and knowing how to get the same result with non-volatile functions, if possible) is also part of being a guru. Though in the example I listed above, I believe that the volatility might be unavoidable. In other words, the Dynamic Named Range structure, even when implemented with non-volatile functions, seems to behave in a volatile manner anyway.

 

[hatman]

one of the main tricks of gurus is that they
use the HELPfiles
and are able to find their way on the internet to get what they need

EXCELLENT point, Erik. I am constantly amazed by people who see what I do, ask where I learned, and then skake their heads when I tell them I taught my own self using Help-Files and Internet Articles. They see that as impenetrable magic.

 

[Oorang]

On the OFFSET function, I tend to really like the MATCH/OFFSET combination for mining data in weird ways. It's slightly more powerful than VLOOKUP (with of course the warnings about volatility as mentioned).
An example would be if you had your data laid out like this "Account#|Name|Balance" and you wanted the name of the guy with the biggest balance you could do =OFFSET(B1,MATCH(MAX(C:C),C:C,0)-1,0).

 

[hatman]

On the other hand, if you are looking for a single cell, Index() will get you there without the volatility of Offset(). As a general rule of thumb, I try to reserve Offset() for those cases where I need a Range or Array as a result, and Index() for all others. In your example, you can do this instead, with the same results:

=INDEX(B:B,MATCH(MAX(C:C),C:C,0))

 

[Oorang]

Good advice hatman

 

[PaddyD]

"On the other hand, if you are looking for a single cell..."

FWIW, index() can return a range:

"INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells within array."

...not to mention the index():index() idiom