An Excel Helper's Cheat Sheet

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
577
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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.
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
577
Office Version
2016
Platform
Windows
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

VBA Code:
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
577
Office Version
2016
Platform
Windows
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.
use2.png
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
577
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Jun 17, 2011
Messages
2,176
Office Version
365, 2016
Platform
Windows
Like one below?
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.
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
577
Office Version
2016
Platform
Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,776
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.
 

Forum statistics

Threads
1,081,570
Messages
5,359,655
Members
400,543
Latest member
nector

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top