An Excel Helper's Cheat Sheet

[DataBlake]

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]

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]

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]

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]

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]

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]

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]

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]

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

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]

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.