| MS Office Stuff
Sections
- Need Office but don't wanna spend the money - www.openoffice.org
- MS Office 2007 - switch back to old menus http://www.addintools.com/english/menuoffice/
- Currupt files - www.officerecovery.com
- Viewers
- MSOffice History - Version number to years
http://en.wikipedia.org/wiki/History_of_Microsoft_Office
Release date Title/version
11/19/1990 Office
1.0
3/4/1991 Office 1.5
7/8/1991 Office
1.6
8/30/1992 Office 3.0
1/17/1994 Office
4.0
6/2/1994 Office 4.3
7/3/1994 Office for NT
4.2
8/30/1995 Office 95 (7.0)
11/19/1996 Office 97
(8.0)
6/20/1998 Office 97 Powered by Word 98
(8.5)
6/7/1999 Office 2000 (9.0)
5/31/2001 Office XP
(10.0)
11/17/2003 Office 2003 (11.0)
1/30/2007 Office 2007
(12.0)
6/15/2010 Office 2010 (14.0)
10/11/2012 Office 2013
(15.0)
Excel Stuff
-
VB/Macro code and examples - see link
above
-
-
-
Date - turn a date with time text to datetime - 5
hours
-
-
Reference cell in Header or
Footer
-
Sub UpdateFooter()
ActiveSheet.PageSetup.Leftfooter = Range("A1").Value
End SubSub UpdateHeader()
ActiveSheet.PageSetup.Leftheader = Range("A1").Value
End Sub
-
Total an array -
Ctrl+Shift+Enter
-
When an array formula is displayed, it is surrounded by
braces {}. You do not enter the braces. Instead, when you enter
an array formula, you press Ctrl+Shift+Enter, rather than just
Enter. Excel will automatically add the braces. You must press
Ctrl+Shift+Enter when you first enter an array formula, and also
each time you edit the formula later. If you enter or array formula
without pressing Ctrl+Shift+Enter, it will return an incorrect
result or a #VALUE! error.
-
define a print routine
-
Private Sub CommandButton1_Click()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Roster").Select
-
End Sub
-
if printing to PDF, see other note in this
section
-
insert/edit a button on sheet (to call a macro or VB
script)
-
turn on Forms AND Visual Basic - "View,
Toolbars, Visual Basic" / "View, Toolbars, Forms"
-
choose the button from FORMS toolbar and format it the way
you want
-
once complete OR your need to edit it, press and unpress
the "Design Mode" button on the Visual Basic toolbar
-
print multi-page to PDF (not separate print jobs) -
-
ISSUE RESOLUTION:
When printing,
Excel will split worksheets within a workbook into separate print jobs if
the resolutions are different. Normally, when printing to a real printer,
this is of no concern. The pages come out sequentially and the user doesn't
care that each page may have been a separate print job.
If "printing" to
a PDF, using Adobe Acrobat or other PDF creation software, however, the
temporary file needed for creation of the PDF file may be overwritten and
only contain the LAST print job.
-
SOLUTION:
Do the following for EVERY
worksheet page in your Excel workbook: File~Page Setup. On the "Page" tab,
set the "Print Quality" to be identical on every page.
-
hide zeros - To hide zero values, type
0;-0;;@ in the Type box. To hide all values, type ;;; (three semicolons) in
the Type box. The hidden values appear only in the formula bar, or in the cell
if you edit within the cell. The values will not be printed.
-
show formulas for whole spreadsheet -
CTRL+~ (thats control key plus the tilde key (the eyebrow looking thingy next
to the 1 key))
-
Age formula - year(date1 - date2) -
1900
|
| A
| B
| C |
| 1
| Based on Date
| 8/31/2005
| |
| 2
| Birthday
| 1/13/1997
| |
| 3
| Age
| 8
| B3 formula is =YEAR($b$1-b2)-1900
format b3
as number no decimals |
-
CountIf - count a column if meets or don't
meet a criteria - COUNTIF(range,criteria)
ex:COUNTIF(B3:B6,"<>good")
-
remove dups in a list - =IF(COUNTIF($A$1:A1,A1)=1,A1,"")
-
MATCH - compare a value to a list of values
- MATCH(lookup_value,lookup_array,match_type) ex:
=IF(MATCH(F3,SHEET2!$A$2:$A$219,0)>0,"good","
not found")
-
Month(Abbr) -
=CHOOSE(MONTH(L2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
-
Month(Full) -
=CHOOSE(MONTH(L2),"January","Feburary","March","April","May","June","July","August","September","October","November","December")
- Day(Full) -
=CHOOSE(WEEKDAY(C24),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
-
Day(Abbr) -
=CHOOSE(WEEKDAY(C24),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
-
dont show the "N/A" on an
error
=IF(ISNA(whatever)," ",whatever)
-
get sheetname
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
Outlook Problems
searching for resolutions - also use OL, OL2K,
OLXP
Tools - www.slipstick.com
Recovery - PST recovery or Exchange, look in
Exchange area
error opening attachments
registry loc
of temp file loc - http://www.howto-outlook.com/faq/securetemp.htm
Outlook autocomplete (Outlook 2003 and
2007)
c:\Documents and Settings\USERPROFILE\Application
Data\Microsoft\Outlook\outlook.nk2
editor - http://www.nirsoft.net/utils/outlook_nk2_autocomplete.html
Outlook 2007 error "Operating system not found OR
Outlook not installed for current user"
-create a new profile and
try again
-more than likely you tried to reuse an old
profile
Save pictures as they were sent (not BMP) - http://www.howto-outlook.com/howto/saveembeddedpictures.htm
Outlook blocking attachments
ADD string value "Level1Remove" then make value
".bat;.exe;.app" (or whatever extension you want)
here is the actual exported key if you wanna save it to a reg
file and import it
[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Security]
"OutlookSecureTempFolder"="C:\\Documents
and Settings\\Stan\\Local Settings\\Temporary Internet
Files\\Content.Outlook\\BKZ02GJ0\\"
"Level1Remove"=".bat;.exe;.app"
External App needing to send email thru Outlook, check
CDO
Error message: "The view is damaged and the default view
will be used"
- run "outlook /cleanviews"
Error : "Outlook locks or blows up when hi-liting a new
folder"
Error : "Outlook has number of unread message, but all
in box are read"
Information store(database) needs repaired
see Exchange 2000 section, and run "isinteg -s servername -fix
-test testname"
File purposes
Tools
scanpst.exe - fix any errors in pst file
Outlook 2007
Can't close or use any menu items, also cant type a
rely
usually happens on new user -
its a hidden dialog
go into Word and put in Name and
Initials
You are here: Home-Computer Tips & Help-PC-MS Office
Previous Topic: Client OS Next Topic: Browser Subtopics: Excel/Macro/VB code
|
