# An Excel Helper's Cheat Sheet



## DataBlake (Dec 27, 2019)

Does anyone have a page they defer to with quick links, answers, etc?
Thinking about making a reference page or FAQ for users who can't be bothered to search for keywords. Either to link to a page that answers the problem in it's entirety or if there is a decent amount of customization to a formula/macro to have a quick copy/paste template.

Common threads i see are things like:
combining multiple sheets into one
userform to worksheet/table
index/match or multiple criteria lookup
web scraping
nested loops/formulas
and many more

if i were to create such a cheat sheet or FAQ is there an interest in sharing such a thing?
I just find myself writing a lot of the same types of answers/macros, which yes, do have answers in various websites (chip's site per say)
but i haven't found a top asked questions type document/page with workable templates etc

tl:dr an excel cheat sheet for the helpers and not necessarily the end users


----------



## Johnny C (Jan 8, 2020)

I think a sheet with generic VBA for common requests, for example a generic sub or function to create/send an Outlook item.

Apart from saving people answering the same question, having discrete sub/functions teaches people about the benefits of making structured subroutines that can be reused. 

One I think people would benefit from is a good guide to getting data from tables, to explain the benefits/limitations of each (Vlookup vs. Index/Match vs. Offset/Match vs. SUMIFS vs. Sumproduct(--form) vs. 

s vs. Pivot tables). People tend to learn one that works for them (usually Vlookup) and stick to it not realising there are a lot of ways and sometimes the way one they use isn't the best for a particular circumstance.


----------



## DataBlake (Jan 8, 2020)

Johnny C said:


> One I think people would benefit from is a good guide to getting data from tables, to explain the benefits/limitations of each (Vlookup vs. Index/Match vs. Offset/Match vs. SUMIFS vs. Sumproduct(--form) vs.
> s vs. Pivot tables). People tend to learn one that works for them (usually Vlookup) and stick to it not realising there are a lot of ways and sometimes the way one they use isn't the best for a particular circumstance.



I appreciate the input.
I heavily rely on VBA and avoid formulas in most situations so i would need someone who is well versed in creating a generic formula for index/match and the likes
Aside from that i have started working on this cheat sheet of sorts that will be sorted like such:

Each sheet has a Theme (say "VBA Loop Basics" for example)
On each sheet will have simple titles like "Loop Through Worksheets" or "Loop through rows"
Double clicking a title will copy a template to your clipboard

So say for instance you're helping someone who wants to know how to loop through all of their worksheets and put "Hello" in A3 of each worksheet
This would be a fast way to get the template like


```
Sub loopworksheet()
Dim y As Long

For y = 1 To ActiveWorkbook.Worksheets.Count
    'insert code here
Next y

End Sub
```

and then you just put your range = value where "insert code here" is and be on your way
obviously it isn't NECESSARY, but for more complicated things it could certainly shave down a lot of time having templates at the ready.
does that make sense?


----------



## Johnny C (Jan 8, 2020)

Where i can see this not working is that someone (an MVP) would need to own each one, as there are lots of opinions as to the best way

For example, i would loop through sheets using
`Dim sht as worksheet 
For each sht in ActiveWorkbook.Sheets
  X=sht.Cells(1,1).Value (e.g.)
  ... Do something 
Next sht`


----------



## DataBlake (Jan 8, 2020)

Johnny C said:


> Where i can see this not working is that someone (an MVP) would need to own each one, as there are lots of opinions as to the best way



i agree that not only are there differing opinions but alternative "best ways" for different operating systems, excel versions, etc
hence why i asked in my first post if this had been done before, but i think treating the file as we do with XL2BB where any updates/better codes would be updated to the host file and readily available to download; would be efficient. 

So far its just a file for me to reference as a cheat sheet, but lets say Mr Excel wants to utilize something like it:
All MVPs have access to changing the document and upon doing so release a new "patch" of the file if they deem something to be considerably more effective.
Just like you think using "for each sheet" as opposed to a "counting loop"
not to mention each iteration could be available

so the example here if someone wants to loop through each sheet we would use your method in the clipboard
and then if someone wants to loop through each sheet except the first sheet then my method would go to the clipboard and the helper would just change the "for y = 1" to "for y = 2"

so instead of the idea of double clicking a title. Instead a title you can have the parent title and child titles in data validation and selecting will copy to clipboard (obviously more organized and pretty than what i have going on right now.


----------



## Johnny C (Jan 8, 2020)

Exactly, there are a ton of ways, and on reflection there are too many variations for it to work. There are plenty of resources with 'standard' templates out there, free and paid for


----------



## DataBlake (Jan 8, 2020)

Johnny C said:


> There are plenty of resources with 'standard' templates out there, free and paid for



regardless im making one for me even if i'm the only person who uses it.  ?
my laziness knows no bounds


----------



## taurean (Jan 12, 2020)

Like one below?




__





						Popular and Useful Excel Posts
					

Please find below a list of Popular and Useful Forum Posts These are compiled from contributions by the Excel Ninja's  =========================================  Concatenate a Range of Cell's includes ConcatIf a condition...



					chandoo.org
				



I am not 100% sure if I have seen similar discussion on MrExcel. 

Other than that, there's a vault section on Chandoo for storing useful bits of codes etc.




__





						The Vault
					

A collection of tips, VBA code samples, examples & tricks shared by our members



					chandoo.org


----------



## DataBlake (Jan 12, 2020)

taurean said:


> Other than that, there's a vault section on Chandoo for storing useful bits of codes etc.
> 
> 
> 
> ...



this is insanely close to what i was referring to thank you. just wish it was organized in categories, but beggars cant be choosers.


----------



## mikerickson (Jan 12, 2020)

The problem I see is that if a questioner isn't going to use the Search function, then they aren't going to use a Commonly 
Asked Questions page either.

Plus, not all users are equally adept at modifying solutions to meet their situation.


----------



## taurean (Jan 13, 2020)

BlakeSkate said:


> this is insanely close to what i was referring to thank you. just wish it was organized in categories, but beggars cant be choosers.


Also there is famous Hiker's list on MrExcel which I remembered later.








						Splitting Single Column Data Into Three
					

Hi All!  I am having data in a single column ..nearly 10,000 cells..     column A  1 FLOWERS ROSE JASMIN TULIP 2 DOGS BULLDOG GERMANSHEPHERED    I Would like to split this data AS    Column A    Column B   Column C      1          FLOWERS    ROSE     1          FLOWERS    JASMIN     1...




					www.mrexcel.com
				




@mikerickson As I understood this post is for helper for quick search and not for end users per se. I might be wrong!


----------



## DataBlake (Jan 13, 2020)

taurean said:


> @mikerickson As I understood this post is for helper for quick search and not for end users per se. I might be wrong!



correct!
I understand end users will be end users. I just want a quick and easy way to navigate useful info to share, templates, and formula basics.
What you've supplied is SO GOOD. i love it thank you.


----------



## CephasOz (Mar 24, 2020)

In the same vein, but for personal use, I have a lot of reusable code stored in a "The Guide" file.  The Guide uses outlining, so I can keep my code in a structured fashion.  (For example, I have a node called "Sort", with a variety of different sort functions as its child nodes.)  Whenever I want a piece of code to do something, I open The Guide, and using the structure can usually go straight to the code, copy, paste, and done!  Sometimes, when a piece of code can be stored in several different places in The Guide, and I am looking in the wrong place, I just use Ctrl-Shift-F to find what I'm looking for.  And because there is a portable version - see The Guide Portable (two-pane outliner) | PortableApps.com - I can load it onto a USB stick and use it on any computer.  The only complaint I have with The Guide is that it doesn't understand VBA, so everything is mono-coloured text.  (The Guide will also store entire files and graphics, not just text, if you need that.)

A side-effect of doing things this way is I only paste the code that I actually use into a given project.  Before I used to wholesale import modules, because 60% of the code in a given module might be used.  Now I just compile, and when it complains that a sub/function isn't present, I copy and paste, then repeat the compile to pick up the next one, until there are none missing.  Only the necessary code is then present.


----------



## DataBlake (Mar 25, 2020)

CephasOz said:


> In the same vein, but for personal use, I have a lot of reusable code stored in a "The Guide" file.  The Guide uses outlining, so I can keep my code in a structured fashion.  (For example, I have a node called "Sort", with a variety of different sort functions as its child nodes.)



Thats a super interesting way to keep organized. Its pretty much the same concept only mine wouldn't be for personal use. I am still working on my helper workbook, but once i'm finished with it i will share.
My version of this emphasizes ease of access to code and automatically copies templates to your clipboard after making a selection, I've even been considering writing entire responses to the most generic questions like "multiple criteria vlookup" and match/index type of things as they are one in the same, just pointing to different columns. I also happen to be a fan of teaching someone how to do things as opposed to just giving them a solution (even though most of the time i just give them the solution)


----------

