# Passing on your knowledge offline



## gingerafro (Aug 7, 2009)

Many of the members who frequent this site often quote "I'm the go-to person for Excel at my work" or "I'm regarded as the Excel guru".

What I want to know is what has been the most useful / widely used thing that you have learned from MrExcel.com that you have incorporated into many reports / passed on to others in your office?


----------



## DonkeyOte (Aug 8, 2009)

for me - avoid Volatiles and Arrays... efficiency is key.

that was of course in the days when I had an office to go to...


----------



## SydneyGeek (Aug 9, 2009)

I've learned more about formulas on this site than anywhere. The first one that stuck was SUMPRODUCT, and I keep seeing new ways to (ab)use it.

On the VBA side, the Dictionary object and regular expressions come to mind, but like many on this site I have learned heaps from posting answers to threads, and often being dragged into new territory as I (and others on the thread) try to come up with an answer to a challenging problem.

Denis


----------



## NateO (Aug 9, 2009)

The most important thing I've learned from MrExcel.com? Excel's software and that it has limitations, but the margin is wider than you might think.

This question is hard to answer, what does your colleague want to do? Use Vlookup(), interface Excel with Lotus Notes, etc...?

As a start, this is where I'd begin:

http://msmvps.com/blogs/nateoliver/archive/2009/06/26/introduction-to-excel-top-5-functions.aspx

But, note the comments, Ken makes a good point. I've left SumProduct() off of there, as step #6.


----------



## Jon von der Heyden (Aug 10, 2009)

I'll echo comments made by Denis and DonkeyOte.  Sumproduct is by far my favourite worksheet function, and I try wherever possible to avoid complex arrays and volatile functions.  I am also very fond of SUM(SUMIF()) formula using inline arrays (learned from Colin_L): http://www.mrexcel.com/forum/showpost.php?p=1822381&postcount=7
And I also frequently use SUMPRODUCT(SUMIF()) for conditional sum across many sheets, although this uses volatile INDIRECT function: http://www.mrexcel.com/forum/showpost.php?p=1335915&postcount=5

But also I am using alot of ADO now because many of my models involve huge volumes of data and I find ADO (and use of pivot tables) very very efficient.  MrExcel has been a very useful resource for me on this.  I can usually find the answer using search, plenty of posts on this subject.


----------



## texasalynn (Aug 10, 2009)

Sumproduct thanks to Aladin!!!!  Also learning macros - was intimadated but slowly coming round to it!


----------



## lenze (Aug 10, 2009)

All of the above are very good observations, but I think the Pivot Table is by far the best. Create a good for your boss and it will keep him/her entertained for hours, allowing you do do the *real work.*

lenze


----------



## Greg Truby (Aug 10, 2009)

You ask: 


gingerafro said:


> What I want to know is what has been the most useful / widely used thing that you have learned from MrExcel.com *that you have incorporated into many reports / passed on to others in your office?*


 
To which I would say I'm with Lenze: Pivot Tables are far and away what I have *shared the most* and *incorporated into many (many, many) reports.* I've shown people how to do sumproducts and vlookups and basic VBA and named formulae and customizing menus and userforms and just about everything else at some point or other. But what will live on after I leave the company? I expect that dozens of pivot table-based reports would live on the longest; along with a couple of pretty good utility add-ins.


----------



## TinaP (Aug 10, 2009)

I'd like to say Pivot Tables, but I haven't really shown anyone how to fully implement them.  Usually I set them up and the users play with them until they break.  Then I can set it up all over again.


----------



## Atroxell (Aug 12, 2009)

Kind of along the same lines with Pivot tables is the use of the offset() function to define ranges. 

I have a lot of people here that use Excel for record keeping and build graphs off the tables they create. When I started defining the table range with offset() and used refreshall in the worksheet level code, they were amazed to see that they only had to enter data and not rebuild the whole workbook each time they added a record. 

Saved TONS of work around here with that litttle tidbit.

Thanks to all of you!


----------



## Jon von der Heyden (Aug 13, 2009)

Atroxell said:


> Kind of along the same lines with Pivot tables is the use of the offset() function to define ranges.
> 
> I have a lot of people here that use Excel for record keeping and build graphs off the tables they create. When I started defining the table range with offset() and used refreshall in the worksheet level code, they were amazed to see that they only had to enter data and not rebuild the whole workbook each time they added a record.
> 
> ...


 
Hi

Offset is one of the volatile worksheet functions that some of us prefer to avoid to ensure efficient models.  http://www.decisionmodels.com/calcsecretsi.htm

Another way of creating dynamic ranges may be to use INDEX instead.  The following:

=OFFSET($A$1,,,COUNTA($A:$A)) could also be represented as:
=$A$1:INDEX($A:$A,COUNTA($A:$A))

Just thought you might find this useful.


----------



## Darren Bartrup (Aug 13, 2009)

I've passed on so much knowledge from MrExcel.

The main one's that have been taken up would be 

SUMPRODUCT

Variable named ranges - which I'm going to have to look at again now using INDEX (thanks Jon)

FuzzyLookup - which I've been asked to convert into an SQL function on Oracle
Then there's the snippets of code I've incorporated into my projects such as

Creating a pivot table on the fly

Creating a chart on the fly

Adding controls to a form at run-time and capturing their events

And not forgetting the other site's I've found through the forum:

http://peltiertech.com/

http://www.rondebruin.nl/

http://spreadsheetpage.com/

The list is endless.... so many things I've learnt, and yet to learn


----------



## Atroxell (Aug 13, 2009)

Jon,

Your response is EXACTLY why I love this forum so much. It's the little things that make the difference, and this site is an eruption of ideas, explanations and reason. Thanks so much!

It would probably be a good idea for me to do some searching for lists of volatile and non-volatile functions--well, I have some homework to do!

I will definitely incorporate your suggestions into my next generation of workbooks and will modify older workbooks as the availability arises.

Thanks again to all who are involved!

-----------------------------------------

Just went to your posted link, Jon. Most excellent! Thanks.


----------



## arkusM (Aug 13, 2009)

Jon von der Heyden said:


> Hi
> 
> Offset is one of the volatile worksheet functions that some of us prefer to avoid to ensure efficient models. http://www.decisionmodels.com/calcsecretsi.htm
> 
> ...


 
Jon,

Will a volatile function calculate in a named range? The link you gave says that volatile functions calculate on a hair trigger, and it also says that function in named ranges only calculate when called specifically, so would a volatile function within a named range be as touchy?

I use the =offset(...) in names ranges to define dynamic ranges.

Cheers,

M


----------

