Excel Magic Trick #506: Summarize Data - Multiple Sheets & Banks Accounts - Checkbook Register
Take Checkbook Register data from multiple sheets and for multiple banks accounts and summarize it based on Account Names. See these tricks: 1.Formula to display different chart of accounts for different companies 2.Formula to summarize account data from different sheets and for different banks accounts 3.Dynamic Formulas that choose different Chart Of Accounts and Different Calculations using the amazing CHOOSE function. 4.See how to use SUMIF to add from some worksheets but not others. 5.See the functions IF, ROWS, COUNTA, CHOOSE, VLOOKUP, INDEX, SUMIF 6.Create Names From Selection 7.Data Validation List 8.Dynamic Ranges using Table / List feature
Thank you
Probably a really hard nasty array formula is required.
You will have to go to the Playlist titled:
Excel Extract Records From Database Table / List
there are Filter, Advanced Filter and Array Formula solutions.
This tutorial is really helpful. What i want is not sum up the total but just have what ever data i alreay have just populate depending on what ever year i select.
Try this video:
Excel Array Formula Series #7: Boolean Logic & Criteria
Data Validation help required please.
I have sales months (Jan-dec) in columns B - M and sales reps in rows and have the total sold populated inside the table. I have this for 2005 - 2009 on each sheet
all the sales are on each sheet
What i want to do is have a data validation 2005 - 2009 and what ever year i select, i want all the sales to be populated for that year.
What formula can i use, Thanks
Becasue you have multiple names and spaces, I am not sure hot to do it. Please post to Mr Excel Message Board. Someone there will know!
SEARCH is not case sensitive, FIND is.
Here is what I know:
Excel Magic Trick 292: Reverse Last & First Name Tricks
Excel Magic Trick #192: Extract Middle Name Text To Column
Excel Magic Trick #191: Extract Middle Name Formula
Highline Excel Class 13: Text functions & AMPERSAND
Excel Magic Trick #190: Rearrange First Last Name w Formula
Excel Magic Trick 511: Extract First, Middle, Last Names From Bad Data With Extra Characters
It totally depends on what data you have, how it is set up, whet goal is. Here are some videos:
Excel Magic Trick 292: Reverse Last & First Name Tricks
Excel Magic Trick #192: Extract Middle Name Text To Column
Excel Magic Trick #191: Extract Middle Name Formula
Highline Excel Class 13: Text functions & AMPERSAND
Excel Magic Trick #190: Rearrange First Last Name w Formula
Excel Magic Trick 511: Extract First, Middle, Last Names From Bad Data With Extra Characters
STEVEN JOHN PAUL GERRARD
MICHAEL JOHN H ANTHONY SMITH
SHAUN H NORMAN
ROGER CLARK
What is the best method/easiest formula to extract any middle names and what’s the difference between the search and find?
Are there any videos on this type of scenario?
gracias
Hi,
I have seen many different ways to extract middle names and last names. Some are really confusing to figure out.
Some people use find and others use search to find a pattern occuring.
What is the best and easist way/formula to use to extract middle and last names. I guess the first name is pretty easy as everyone seems to extract the first name using the same formula. What if there is 1,2 or more middle names…i,e
You are welcome!
No but I have learned a ton of great tricks from you videos that I’ve used at work. Most recently I’ve utilized that VLOOKUP function. Boy that is a great thing to know. Thank you for all the work you put into these videos.
Thank You
I do not know how to do that. The best Excel resource is:
Mr Excel Message Board
mrexcel[dot]com/forum
I I have commenetd in about 200 of the 1000 videos posted - THE SMARTEST EXCEL PEOPLE IN THE WORLD HANG OUT AT THIS SITE!!!
Is there a formula/trick etc.. where i can copy and paste on each sheet but have the dates change/added by a week and also have the sheet name change automatically to WC 15/02/2009, WC 22/02/2009 ETC…
SO SHEET 1 (WC 15/02/2009)
SHEET 2 (WC 22/02/209)
SHEET 3 WC 29/02/2009)
So just to make it clear i want to copy and paste accross the same data for each week but have the dates increment by week to week automatically and also have the sheet tab name change to the WC etc…
Thanks
I have several of other data i.e employee names, start times, finish times etc….but the main thing i want is have the same data copied accross but have the week change from 15/02/09 - 20/02/2009 to 22/02/2009 - 27/02/2009. So basically as you can imagine i am doing a weekly roster but its a pain to keep copying and pasting and having to change the dates manually.
Cool thank you
I need some help. Please
I am trying to do a weekly roster for work.
So just say Column A1-E1 i have todays date (15/02/2009) Monday in Column A1 and i have the rest of the days for the week added accross to E1 therefore in Column E1 the date is (20/02/2009) Saturday. (Sunday is a non rostered day).
See my playlist on Charts.
The first few videos show and compare different charts. Then there are many specific Chart Type videos…
Hi
You will definately be the man to know. Can you explain what all the charts are in excel. There are many charts i.e gannt chart, bar charts, line charts, pie charts etc… but i dont know what charts are used to analyse what type of query or when to use what charts to analyse data.. I.e when do we use a bar chart, when do we use pie charts, when do we use the gannt chart etc….
Thank You
Thank You
I will watch them and i managed to find the playlists so thank you
It probably won’t happen any time soon. But that doesn’t matter. You can teach yourself the Excel Basics, then forget template that are already made, just make your own. Watch these playlists to get the basics:
Excel Basics Series
Highline Excel Class Beg - Adv (Complete Class - 59 videos)
Do you know how to find the playlists?
Thank you for your quick response. Your videos are great. If you could format the videos you have done already on Financial Statements or do some more videos on balance sheets, income statement, profit and loss and cash flow, acc’s receivable, depreciation formulas/templates etc.. that would be fab. You sure have made Excel Fun
I do not have any Financial Statement Videos. Although some of the first videos I made about 4 years ago were 57 videos for an accounting class, I have not translated any of them to a format that YouTube will accept. Perhaps I will do that sometime soon…
Hi,
Do you have any videos on how to do balance sheets, income statement, profit and loss and cash flow formulas/templates
If no, is there any chance you could do a video on balance sheets, income statement, profit and loss and cash flow formulas/templates
I have bought a mini supermarket and will like to know how to set up my own data (balance sheets, income statement, profit and loss and cash flow formulas/templates)
Thank you and your videos are awesome
Were you the one who asked this question originally?