======Microsoft - Office - Excel======
[[http://www.dailydoseofexcel.com/|Daily Dose of Excel - Daily posts of Excel tips…and other stuff]] \\
[[http://www.rondebruin.nl/index.html|Ron's Excel Page]] \\
=====Documentatie=====
**[[http://msdn.microsoft.com/en-us/library/bb190882(office.11).aspx|MSDN - Office 2003 - VBA Language Reference]]:** \\
* [[http://msdn.microsoft.com/en-us/library/aa272268(office.11).aspx|Excel VBA Reference]] \\
* [[http://msdn.microsoft.com/en-us/library/aa272097(office.11).aspx|Word VBA Reference]] \\
[[http://branders.name/archives/2005/04/17/excel-function-reference|Branders.name - Excel Function Reference]] bevat link naar [[http://branders.name/files/Excel-Function-Reference.pdf|downloadbare pdf]] met NL en EN namen voor functies binnen Excel. \\
[[http://www.decisionmodels.com/memlimitsc.htm|Excel Memory Limits]] \\
[[http://www.decisionmodels.com/calcsecrets.htm|Excel’s Smart Recalculation Engine]] \\
=====Excel Sheet read-only laten openen=====
Kies voor opslaan, klik op Extra->Algemene Opties...->vinkje bij "Alleen-lezen aanbevolen"
=====Opstartmappen=====
[[https://web.archive.org/web/20050210130817/http://support.microsoft.com/kb/291218/EN-US/|archive.org - Microsoft Support - How to use Excel startup folders in Excel 2002]] \\
[[https://support.microsoft.com/en-us/office/customize-how-excel-starts-6509b9af-2cc8-4fb6-9ef5-cf5f1d292c19|Microsoft Support - Customize how Excel starts]] \\
[[https://support.microsoft.com/en-us/office/automatically-open-a-specific-workbook-or-template-when-you-start-excel-be9cddc1-a148-4cbf-8f3b-779df08957a3|Microsoft Support - Automatically open a specific workbook or template when you start Excel]] \\
=====Disable Excel 2007+ file extension warning=====
Via registry:
Windows Registry Editor Version 5.00
// For Excel 2007
[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security]
"ExtensionHardening"=dword:00000000
// For Excel 2010
[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Security]
"ExtensionHardening"=dword:00000000
Via Group Policy:
User Configuration\Policies\Administrative Templates\Microsoft Excel 2007\Excel Options\Security \\
Force file extension to match file type: Enabled, Allow different
User Configuration\Policies\Administrative Templates\Microsoft Excel 2010\Excel Options\Security \\
Force file extension to match file type: Enabled, Allow different
Sources: \\
[[http://devblog.grinn.net/2008/06/file-you-are-trying-to-open-is-in.html|Like a Cheese and Tech Sandwich Without the Cheese or Bread - "The file you are trying to open, '[filename]', is in a different format" Excel Error ]] \\
[[http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/03/11/excel-2007-extension-warning.aspx|MSDN Blogs > VSOfficeDeveloper: Known Problems, Bugs, and Fixes > Excel 2007 Extension Warning On Opening Excel Workbook from a Web Site]]
======Formule snippets======
=====SUMPRODUCT/SOMPRODUCT=====
Om in een sheet het aantal regels te tellen waarbij in Kolom A, B, C, enz. bepaalde waarden voorkomen kun je SUMPRODUCT gebruiken.
Voorbeeld:
^Naam ^OS ^Editie ^OS SP niveau ^SQL/ISA/Exchange/MOSS editie ^SP niveau ^Uitvoering ^Test/Productie ^Functie^
|server1|Server 2003|R2 Standard|SP2|SQL 2005 Standard|SP2|Fysiek|Productie|SQL server project 12|
|server2|Server 2003 x64|Standard|R2|SQL 2005 Enterprise x64|SP2|Virtueel|Test|Test SQL Server project 12|
|server3|Server 2003|Standard|SP2|nvt| |Virtueel|Productie|Applicatie server voor product y|
|server4|Server 2008 x64|Enterprise|SP1|SQL Server 2008 Enterprise x64|SP1|Fysiek|Productie|SQL Server project 1|
Om uit de deze tabel alle virtuele Server 2003 Standard installaties die in productie staan te selecteren:
=SOMPRODUCT(--(Servers!G2:G6="Virtueel");--(Servers!H2:H6="Productie");--(Servers!B2:B6="Server 2003");--(Servers!C2:C6="Standard"))
Bron:[[http://www.contextures.com/xlFunctions04.html|Contextures - Excel -- Worksheet Functions -- Count Cells]] \\
======Tools======
[[http://www.ozgrid.com/Services/ExternalFree.htm|Free Excel Workbooks, Templates, Add-ins, Tutorials & More]] \\
=====AddIns=====
[[http://www.rondebruin.nl/addins.htm|Ron's Excel Page - Excel Add-ins]] \\
[[http://www.businessfunctions.com/|Business Functions]] is a free function library for Microsoft Excel designed to make business plans, budgets, structured financings and cashflows \\
[[https://github.com/sweenig/ipfromdec/|GitHub - sweenig/ipfromdec]] Custom Excel Add-In to work with IP Addresses. Via [[https://stuart.weenig.com/2012/04/custom-formula-ipfromdec-ip-address.html|Stuart's Semi-Professional Blog - Custom Formula: IPFromDEC (IP address from decimal)]]
=====Passwords=====
[[http://www.straxx.com/excel/password.html|Excel Password Remover 2008]] \\
======Troubleshooting======
[[http://www.jkp-ads.com/Articles/StartupProblems.asp|Excel Startup Problems]] \\
=====Microsoft Knowledge Base=====
[[http://support.microsoft.com/kb/280533|Paste Options button does not appear when you paste between two instances of Excel]] \\
[[http://support.microsoft.com/kb/271513/en-us|How to troubleshoot errors when you save Excel files]] \\
=====Problemen bij het openen van .xls bestanden vanuit verkenner=====
Bij het openen van een xls-bestand vanuit Excel wordt het bestand geopend, maar bij dubbelklik op een xls-bestand in Explorer opent Excel wel, maar het bestand niet.
1. Tools>Options>General en haal het vinkje weg bij Ignore Other Applications. \\
2. Sluit Excel vanuit Start>Run. Type in:
C:\Program Files\Microsoft Office\Office11\Excel.exe /unregserver
Voer vervolgens uit:
C:\Program Files\Microsoft Office\Office11\Excel.exe /regserver
Bron:[[http://www.dailydoseofexcel.com/archives/2004/05/05/opening-excel-files-from-windows-explorer/|Opening Excel Files From Windows Explorer]] \\
=====LocaleIdentifier property is not overwritable and cannot be assigned a new value=====
When trying to refresh a pivot table in an Excel sheet with data from a SQL Server Analysis Services cube the following error appears:
{{:microsoft:office:exccel_2010_-_xml_for_analysis_parser.png|}} \\
Workaround: either remove the Locale Identifier property from the connection string in the .odc file and re-add it to the Excel sheet or open the properties of the connection in the Excel sheet and place a checkmark in the box "Retrieve data and errors in the Office display language when available".
Source:[[http://support.microsoft.com/kb/931388/en-us|Microsoft Support - Error message when you update data in a PivotTable in Excel 2007: "Excel was unable to get necessary information about this cube" or "LocaleIdentifier property is not overwritable and cannot be assigned a new value"]]