Passing on your knowledge offline

[gingerafro]

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]

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]

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]

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]

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]

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

 

[lenze]

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]

You ask:

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]

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]

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!