Finance 3.0 - Social Network for Finance
Smart financial thinking
SECTION 1 - FORMULAS
FIND THE FIRST NON-BLANK VALUE IN A ROW 2
CALCULATE WORKDAYS FOR 5, 6, 7 DAY WORKWEEKS 4
STORE HOLIDAYS IN A NAMED RANGE 9
SUM EVERY OTHER ROW OR EVERY THIRD ROW 10
WHY THE MINUS MINUS? COERCE NUMBERS FROM TRUE/
FALSE 12
INTRODUCING THE BORING USE OF SUMPRODUCT 15
UNDERSTAND BOOLEAN LOGIC: FALSE IS ZERO; AND IS
*,OR IS + AND EVERYTHING ELSE IS TRUE 16
USE GET.CELL TO HIGHLIGHT NON-FORMULA CELLS 20
REFER TO A CELL WHOSE ADDRESS VARIES, BASED ON A
CALCULATION 27
POINT TO ANOTHER WORKSHEET WITH INDIRECT 30
GET DATA FROM ANOTHER WORKSHEET BY USING
INDIRECT 34
USE INDIRECT TO GET A DATA FROM A MULTI-CELL RANGE 36
ALWAYS POINT TO CELL B10 37
USE NATURAL LANGUAGE FORMULAS WITHOUT USING
NATURAL LANGUAGE FORMULAS 38
SUM A CELL THROUGH SEVERAL WORKSHEETS 39
SUM VISIBLE ROWS 40
LEARN R1C1 REFERENCES 43
RANDOM NUMBERS WITHOUT DUPLICATES 46
SORT WITH A FORMULA 49
DEAL WITH DATES BEFORE 1900 51
USE VLOOKUP TO GET THE NTH MATCH 52
USE A SELF-REFERENCING FORMULA 55
USE TWO-WAY INTERPOLATION WITH A SINGLE FORMULA 57
FIND THE SUM OF ALL DIGITS OCCURING IN A STRING 61
GET AN ARRAY OF UNIQUE VALUES FROM A LIST 62
AUTO-NUMBER RECORDS AND COLUMNS IN AN EXCEL
DATABASE 65
SECTION 2 - TECHNIQUES
USE AUTOFILTER WITH A PIVOT TABLE 70
SORT SUBTOTALS 71
COPY AN EXACT FORMULA BY USING DITTO MARKS 72
RIGHT-DRAG BORDER TO ACCESS MORE COPYING OPTIONS 73
QUICKLY CREATE A HYPERLINK MENU 75
QUICKLY CREATE MANY RANGE NAMES 76
ADD FORMULAS TO SMARTART 79
CREATE A PIVOT TABLE FROM DATA IN MULTIPLE
WORKSHEETS 82
DETERMINE THE HEIGHT AND WIDTH OF THE DATALABEL
OBJECT 85
ADJUST XY CHART SCALING FOR CORRECT ASPECT RATIO 88
SECTION 3 - MACROS
MAKE A PERSONAL MACRO WORKBOOK 96
RUN A MACRO FROM A SHORTCUT KEY 100
RUN A MACRO FROM A BUTTON 102
RUN A MACRO FROM AN ICON 109
CREATE A REGULAR MACRO 115
CREATE AN EVENT HANDLER MACRO 116
EXTRACT AN E-MAIL ADDRESS FROM A CELL CONTAINING
OTHER TEXT 118
FIND THE CLOSEST MATCH 121
USE TIMER TO MICRO-TIME EVENTS 123
DISCOVER THE TEMP FOLDER PATH 125
USE EVALUATE IN VBA INSTEAD OF LOOPING THROUGH
CELLS 127
RENAME EACH WORKSHEET BASED ON ITS A1 VALUE 129
USE A CUSTOM PULL FUNCTION INSTEAD OF INDIRECT
WITH A CLOSED WORKBOOK 130
IN VBA, DETERMINE THE NUMBER OF THE ACTIVE WORKSHEET
131
CREATE WORKSHEET NAMES BY USING THE FILL HANDLE 132
COPY THE PERSONAL MACRO WORKBOOK TO ANOTHER
COMPUTER 136
ADD FILTER TO SELECTION FUNCTIONALITY 138
USE A MACRO TO HIGHLIGHT THE ACTIVE CELL 142
REMOVE THE CAPTION BAR FROM A USER FORM 145
KEEP A BUTTON IN VIEW 147
ADD A RIGHT-CLICK MENU TO A USER FORM 148
FORMAT A USER FORM TEXT BOX AS CURRENCY OR A
PERCENTAGE 152
DELETE RECORDS IN VBA 154
SELF-SIGN YOUR MACROS FOR CO-WORKERS 156
MAGNIFY A SECTION OF YOUR SCREEN 160
LIST COMBINATIONS OF N ITEMS TAKEN M AT A TIME 161
APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE 167
- Attachments:
Replies to This Discussion
-
Permalink Reply by jkjiwani on -
thanks a billion..i was just abt to place the order..infact , i had put down in the cart as well.
many thanks..GOD BLESS YOU.
-
Permalink Reply by Yasir Bilal on -
Hi dipu
your file "Excel Guru----" is not opening and there is some error will u please send it to me on my ID as
yasir_aliahmed@hotmail.com
Many thanks
-
Permalink Reply by mehul a raithatha on
-
great addition to the knowledge
-
Permalink Reply by Sachidanand (Sacha) Singh on -
I do not know from where you get all these wonderful e books. Thanks
-
Permalink Reply by Yasir Bilal on
-
please help me i find it difficult to download this file as there is an error message box while opening this file.
-
Permalink Reply by Yasir Bilal on
-
Hi Dipu James,
Dipu please guide me how to get this book as far as my knowledge it is a wounderfull book i am facing an error while downloading this file please guide me or u can send me on this e-mail id yasir_aliahmed@hotmail.com
i shall be very thankfull to u
-
Permalink Reply by Dipu James on -
hotmails do not take big e-mails send me a gmail id or some other id which will take big mails
Cheers "knowledge should be free".
-
Permalink Reply by Yasir Bilal on
-
hi dipu
i don't have gmail id rather a yahoo id as tariqhusyen@yahoo.com
is it ok or i should create a gmail id
Thanks a lot for your time
-
Permalink Reply by Paul Chege on
-
File not opening. Please send it to me via paul.n.chege@gmail.com
Thanks and kind regards.
-
Permalink Reply by Frank Gamadey on
-
Man, you are great! You are such a selfless sharer of knowledge. Please send the attachment to my mail as I am having difficulties downloading them. Kindly add the other pdf stuffs on financial modelling. Thank you
e-mail:fgamadey@gmail.com
Sign in
Badge
Continuing Education
 |
Accounting |
 |
Banking Regulations |
 |
Certification Training |
 |
Commercial Bank Management |
 |
Corporate Finance & Valuation |
 |
Treasury Management |
 |
Credit Risk Management |
 |
Financial Derivatives |
 |
Financial Institution Management |
 |
Financial Markets |
 |
Financial Modeling |
 |
Financial Planning |
 |
Market Risk Management |
 |
Operational Risk Management |
 |
Operations & Compliance |
 |
Quantitative Analysis |
 |
Strategic Financial Management |
Newsletter
Linkedin
RSS
Facebook
Widgets
Twitter© 2010 Created by Finance 3.0.
