Tech Tips for Techs: Programmatically Creating an Excel Spreadsheet

Standard

 

This article will show you how to programmatically create an Excel spreadsheet. I wrote this in VBScript, since it is readily available and easy to translate to other languages. I wrote the code to be modular, and, with the use of a few global variables (yeah, I know), it can be easily customized to meet your needs.
Here is the complete program. Just copy everything into a text editor (or a VBScript Editor if you have one) and save it a “MyDir.vbs.”

'*******************************************************************************
'*** MyDir
'*** Written by Frank Lindsey at Everon IT / PlumChoice
'*** flindsey@everonit.com NO SOLICITATIONS, I have one of those
'*******************************************************************************
'*** Description:
'*** This script will create an Excel Spreadsheet containing the files found in
'*** the DEFAULT_PATH.
'***
'*** Assumptions:
'*** - WScript 5.6+
'*** - Microsoft Excel 2003+
'*** Coding Rules:
'*** - Variable names are in the format of []
'*** - Global names are proceeded with a G
'*** - In  caps are used to delineate words
'*** - s are:
'***      int Integer
'***      str String
'***      obj Object
'***      col Collection of objects
'***      ary Array
'***        l logical
'*** - Constant names are all caps with a "_" used as word a separator
'*** - Subroutine names are in the format of sub
'*** - Function names are in the format of fun
'*** - Code formatted to be viewed at 80 columns
'*** -    No tab characters, indent level is three spaces
'***
'*** Revision History:
'*** Original coding: 07/18/2014 Frank Lindsey
'*** Reformatting:    08/07/2014 Frank Lindsey 
'***
'*******************************************************************************
'*** Public Domain
'*** This script is released into the public domain. You may use it freely.
'*** However, if you make any modifications and redistribute, please list your
'*** name and describe the changes under Revision History.
'***
'*** This script is distributed without any warranty, expressed or implied. If
'*** you choose to use this script, in whole or in part, you agree to take sole
'*** responsibility for any problems that may occur.
'*******************************************************************************

'***[ Initializations ] ********************************************************
Option Explicit 'Verify we are running WScript before we do anything else
If (InStr(LCase(WScript.FullName),"wscript") = 0) Then
   Call subCheckScriptHost()
End If

'*** [ Declarations ] **********************************************************
'**************************************************************
'** User Definable - reset these values to customize the script
'**************************************************************
'Constants
Const DEFAULT_PATH = "C:\Windows\"    'This is the folder we will list
Const REPORT_TITLE = "My DIR Listing" 'This is the complete title
Const SAVE_FILE    = "MyDir"          'This is the output file name

Dim GintRow 'Current row in spreadsheet

'**************************************************************
'** Create Global Objects - Objects used through out the script
'**************************************************************
'Objects
Dim GobjExcel : Set GobjExcel = Nothing

On Error Resume Next
   Err.Clear
   Set GobjExcel = WScript.CreateObject("Excel.Application")
On Error Goto 0 'Any Errors?

If Err.Number  0 Then
   Call subCloseApp("Run time Error.", _
                    Err.Number,        _
                    Err.Description,   _
                    Err.Source)
ElseIf (GobjExcel Is Nothing) Then
   Call subCloseApp("Fatal Error creating Excel object", _
                    Err.Number,                          _
                    Err.Description,                     _
                    Err.Source)
End If

'Integers
Dim GintColumns : GintColumns = 12 'Set to the number of Columns

'Arrays
Dim GaryHeaders()

'Fill the headers. Start with 1 in the arrays
ReDim GaryHeaders(GintColumns)
GaryHeaders( 1) = "Attributes"    : GaryHeaders( 2) = "Created"
GaryHeaders( 3) = "Last Accessed" : GaryHeaders( 4) = "Last Modified"
GaryHeaders( 5) = "Drive"         : GaryHeaders( 6) = "Name"
GaryHeaders( 7) = "Parent Folder" : GaryHeaders( 8) = "Path"
GaryHeaders( 9) = "Short Name"    : GaryHeaders(10) = "Short Path"
GaryHeaders(11) = "Size"          : GaryHeaders(12) = "Type"

'***[ MAIN ]********************************************************************
'*********************
'** Local Variables **
'*********************
'Objects
Dim objFile        : Set objFile        = Nothing
Dim GobjFileSystem : Set GobjFileSystem = Nothing
Dim objFolder      : Set objFolder      = Nothing

On Error Resume Next
   Err.Clear
   Set GobjFileSystem = CreateObject("Scripting.FileSystemObject")
   Set objFolder      = GobjFileSystem.GetFolder(DEFAULT_PATH)
On Error Goto 0

'Any Errors?
If Err.Number  0 Then
   Call subCloseApp("Run time Error.", _
                    Err.Number,        _
                    Err.Description,   _
                    Err.Source)
ElseIf (GobjFileSystem Is Nothing) Then
   Call subCloseApp("Fatal Error creating FSO object", _
                    Err.Number,                        _
                    Err.Description,                   _
                    Err.Source)
ElseIf (objFolder Is Nothing) Then
   Call subCloseApp("Fatal Error creating FSO Folder object", _
                    Err.Number,                               _
                    Err.Description,                          _
                    Err.Source)
End If

'Collections
Dim colFiles : Set colFiles = Nothing

'Arrays
Dim aryFileInfo()

'*******************
'** Start of code **
'*******************
Call subBuildXLS

Set colFiles = objFolder.Files

ReDim aryFileInfo(12)
For Each objFile in colFiles
   With objFile
      aryFileInfo( 1) = .Attributes       : aryFileInfo( 2) = .DateCreated
      aryFileInfo( 3) = .DateLastAccessed : aryFileInfo( 4) = .DateLastModified
      aryFileInfo( 5) = .Drive            : aryFileInfo( 6) = .Name
      aryFileInfo( 7) = .ParentFolder     : aryFileInfo( 8) = .Path
      aryFileInfo( 9) = .ShortName        : aryFileInfo(10) = .ShortPath
      aryFileInfo(11) = .Size             : aryFileInfo(12) = .Type
   End With

   Call subAddLineXLS(aryFileInfo)
Next

'Save all of our work
If funSaveFiles(SAVE_FILE) Then
   MsgBox "Your inventory run is complete!", _
          vbInformation + vbOKOnly,          _
          REPORT_TITLE
End If

GobjExcel.Visible = True 'Show the output Excel file
'***[ End of MAIN ]*************************************************************

'*************
'** Cleanup **
'*************
'Object cleanup
Set GobjExcel = Nothing

WScript.Quit 'Really and truely not necessary

'***[ FUNCTIONS ]***************************************************************
'****************************************************
'** funSaveFiles - Use the Excel Save File Routine **
'****************************************************
Function funSaveFiles(strFileName)
   '*********************
   '** Local Variables **
   '*********************
   'Strings
   Dim strFilter, strTitle, strFullName

   Call subFooter()

   funSaveFiles = False 'Default to fail

   'Configure Save As dialog box
   strFilter   = "Excel File (*.xlsx), *.xlsx"
   strTitle    = "MyDir Save As"
   strFullName = DEFAULT_PATH & strFileName & ".xlsx"

   'Start with a clean slate
   Err.Clear

   'Create the folder if it does not exist
   If Not (GobjFileSystem.FolderExists(DEFAULT_PATH)) Then
      On Error Resume Next
         Err.Clear
         GobjFileSystem.CreateFolder(DEFAULT_PATH)
      On Error Goto 0
   End If

   'Did we fail to create the directory?
   If Err.Number  0 Or Not (GobjFileSystem.FolderExists(DEFAULT_PATH)) Then
      MsgBox "Could not create the folder: '" & DEFAULT_PATH & "'"
   Else
      On Error Resume Next
         Err.Clear

         'Get the filename from user
         strFullName = GobjExcel.GetSaveAsFilename(strFullName, _
                                                   strFilter,   _
                                                   1,           _
                                                   strTitle)
         If Err.Number  0 Then
            MsgBox "Could not save Excel File: '" & strFullName & "'"
         Else
            Err.Clear
            GobjExcel.ActiveWorkbook.SaveAs strFullName
            If Err.Number  0 Then funSaveFiles = True
         End If
      On Error Goto 0
   End If
End Function

'***[ SUBROUTINES ]*************************************************************
'***************************************************
'** subCheckScriptHost - Are we running WScript? ***
'***************************************************
Sub subCheckScriptHost()
   '*********************
   '** Local Variables **
   '*********************
   'Constants
   Const WINDOW_HIDE = 0 'Run Command Window Style

   'Objects
   Dim objShell : Set objShell = Nothing

   'Strings
   Dim strExec

   '*******************
   '** Start of code **
   '*******************
   'Create Objects
   On Error Resume Next
   Err.Clear
   Set objShell = CreateObject("WScript.Shell")
   On Error Goto 0

   'Any Errors?
   If Err.Number  0 Then
      Call subCloseApp("Run time Error.", _
                       Err.Number,        _
                       Err.Description,   _
                       Err.Source)
   ElseIf (objShell Is Nothing) Then
     Call subCloseApp("Fatal Error creating Shell object", _
                      Err.Number,                          _
                      Err.Description,                     _
                      Err.Source)
   End If

   'Restart using WScript
   strExec ="%COMSPEC% /c "         & Chr(34) & _
            "wscript.exe //NoLogo " & Chr(34) & _
            WScript.ScriptFullName  & Chr(34) & Chr(34)
   objShell.Run strExec, WINDOW_HIDE, False

   Wscript.Quit
End Sub

'**************************************************
'** subAddLineXLS - Add Lines to the spreadsheet **
'**************************************************
Sub subAddLineXLS(ByRef aryLineDetail)
   '*********************
   '** Local Variables **
   '*********************
   'Integers
   Dim intCounter, intRows 'Basic Counters

   '*******************
   '** Start of code **
   '*******************
   intRows = UBound(aryLineDetail) + 1 'Number of rows sent
   For intCounter = 1 To intRows - 1
      GobjExcel.Cells(GintRow, _
      intCounter).Value = Trim(aryLineDetail(intCounter))
   Next

   GintRow = GintRow + 1 'We are now on the next row in the spreadsheet
   GobjExcel.Cells(1, 1).Select 'Back to the top
End Sub

'************************************************
'** subBuildXLS - Builds the actual Excel file **
'************************************************
Sub subBuildXLS()
   '***********************
   '*** Local Variables ***
   '***********************
   'Constants
   Const EXCEL_WHITE  =  2 : Const EXCEL_NAVY   = 11 : Const EXCEL_BLACK   =  1
   Const EXCEL_GRAY   =  3 : Const EXCEL_DBLUE  =  4 : Const EXCEL_LBLUE   =  5
   Const EXCEL_MAROON =  6 : Const EXCEL_OLIVE  =  7 : Const EXCEL_LPURPLE =  8
   Const EXCEL_TEAL   =  9 : Const EXCEL_ORANGE = 10 : Const EXCEL_DPURPLE = 12

   Const EXCEL_NONE = 0 : Const EXCEL_SOLID = 1 : Const EXCEL_AUTOMATIC = -4105

   Const EXCEL_LEFT   =     2 : Const EXCEL_RIGHT   =     4
   Const EXCEL_DOWN   = -4121 : Const EXCEL_UP      = -4162
   Const EXCEL_TOLEFT = -4159 : Const EXCEL_TORIGHT = -4161

   'Objects
   Dim objExcelRange : Set objExcelRange = Nothing

   '*******************
   '** Start of code **
   '*******************
   GintRow = 1 'Current row in spreadsheet

   ' Create the spreadsheet
   With GobjExcel
      .Visible = False
      .WorkBooks.Add

      .Sheets("Sheet1").Select()
      .Sheets("Sheet1").Name = REPORT_TITLE
   End With

   Call subAddLineXLS(GaryHeaders) 'Add the headers

   'Define objExcelRange
   On Error Resume Next
      Err.Clear
      Set objExcelRange = GobjExcel.Range("A1",Chr(Asc("A")+GintColumns-1)&"1")
   On Error Goto 0

   'Any Errors?
   If Err.Number  0 Then
      Call subCloseApp("Run time Error.", _
                       Err.Number,        _
                       Err.Description,   _
                       Err.Source)
   ElseIf (objExcelRange Is Nothing) Then
      Call subCloseApp("Fatal Error creating Excel object", _
                       Err.Number,                          _
                       Err.Description,                     _
                       Err.Source)
   End If

   'Format the header line
   With objExcelRange
      With .Interior
         .Pattern             = EXCEL_NONE
         .PatternColorIndex   = EXCEL_NONE
         .TintAndShade        = 0
         .PatternTintAndShade = 0
      End With

      .Font.Bold = True
   End With
End Sub

'**************************************************************
'** subCloseApp - Called to abnormal application termination **
'**************************************************************
Sub subCloseApp(strError, intError, strDescription, strSource)
   On Error Resume Next 'No way out
      '*********************
      '** Local Variables **
      '*********************
      'Strings
      Dim strMessage 'Error message to be displayed

      '*************
      '** Cleanup **
      '*************
      GobjExcel.Visible = True 'Show the output Excel file

      'Object cleanup
      strMessage = strError 'Start with the passed messsage

      'Add any error numbers
      If strError > 0 Then                                          _
         strMessage = "***"                              & vbCrLf & _
                   strMessage                            & vbCrLf & _
                                                           vbCrLf & _
                   "*** UNKNOWN ERROR: ABORTING ***"     & vbCrLf & _
                   "***"                                 & vbCrLf & _
                   " Error: "        & intError          & vbCrLf & _
                   " Description : " & strDescription    & vbCrLf & _
                   " Source: "       & strSource

      MsgBox strMessage, vbInformation + vbOKOnly, REPORT_TITLE

      WScript.Quit 'Abort, Abort, Abort
   On Error Goto 0 'Why? Too keep it clean
End Sub

'***************************************************
'** subFooter - added when speadsheet is complete **
'***************************************************
Sub subFooter()
   '*********************
   '** Local Variables **
   '*********************
   'Constants
   Const EXCEL_BLACK = 1 : Const EXCEL_LEFT = 2

   'Integers
   Dim intCounter 'Basic Counter

   'Arrays
   Dim aryFooters()

   '*******************
   '** Start of code **
   '*******************
   'Feel free to make this what you want
   ReDim aryFooters(2) 'Number of Footer notes + 1
   aryFooters(0) = "My Directory Listing (MyDir.vbs)"
   aryFooters(1) = "Listing for files in path: " & DEFAULT_PATH
   aryFooters(2) = "Written by Frank Lindsey flindsey@everonit.com"

   GintRow = GintRow + 3 'Give us a little room

   With GobjExcel
      .Cells.Select
      .Cells.EntireColumn.AutoFit

      For intCounter = 0 To 2
         GintRow = GintRow + 1

         .Cells(GintRow, 4).Select
         .Selection.Font.ColorIndex     = EXCEL_BLACK
         .Selection.Font.Size           = 8
         .Selection.Font.Bold           = False
         .Selection.HorizontalAlignment = EXCEL_LEFT
         .Cells(GintRow, 4).Value       = aryFooters(intCounter)
      Next

      .Range("A1").Select
   End With
End Sub
'***[ End of SCRIPT ]***********************************************************
Comments

First let’s get the comment section out of the way. This is my standard opening comment section. In it I try to cover my coding style and any assumptions. As you can see, this script requires you have at least WScript 5.6 and Microsoft Excel 2003 (this should not be an issue unless you are still running Windows XP or older).

Initialization

In the INITIALIZATION section I am defining the Constants and Global variables. Most of these variables are the ones you change to customize this script. For example, DEFAULT_PATH is the directory that will be listed and the results placed in the spreadsheet.

Now let’s take a look at the FUNCTIONS and SUBROUTINES.

Functions

The function funSaveFiles handles the saving of files. Pass the filename as a string. The Files System routines used to check for a folder and create one are called if needed. We are able to call the actual Excel Save (GetSaveAsFilename) routine to save the file we created. This is nice, since all we need to do is define the file name (strFullName), display filter (strFilter), and the window title (strTitle) – Excel will take care of the rest.

MyDir 1

Subroutines

The subroutine subCheckScriptHost is a standard Subroutine I use in most of my code. It tests to see if WScript is running, if not it will start WScript and re-run the program for you. The main reason I choose to use WScript (as opposed to CScript) is that WScript uses MessageBox for standard output and CScript writes a line to the console window.

The subroutine subAddLineXLS   will simply add a new row of data to the spreadsheet. Just pass an array containing the data, one array object for each column in the row to be added.

The subroutine subBuildXLS will create the actual spreadsheet. I set Visible to False so the actual spreadsheet is not displayed until it is completed.  Some interesting things that are done in this subroutine are:

  • Add the headers by calling subAddLineXLS()
  • Select a range and make it an object: SetobjExcelRange = GobjExcel.Range()
  • Format the Object Range by setting the values objExcelRange.Interior…

The subroutine subCloseApp closes out the script and is called for normal and abnormal (errors) termination. The first thing I do is turn off error handling so if my exit routine returns an error, the script still terminates. Again, this is one of my standard subroutines.

I always like to put a footer on any report I create. The subroutine subFooter is straightforward and will create a custom footer based on a locally defined array. To customize, change the array size to the number of lines – 1. Finally, add an array object (starting at 0) for each line you want to add.

Coding Overview

A quick high level coding overview:

  • Check to see if we are running under WScript – subCheckScriptHost
  • Create New Excel File – subBuildXLS
  • Build and add the Header – subAddlineXLS
  • Add a line for each row of data – subAddLinesXLS
  • Create the footer – subFooter
  • Save the file and clean things up – subCloseApp

One more point. As you can see, I created my own Error Routine. It is very basic, as I just turn off error handling and then call my exit subroutine, if there is any error. I pass a custom message, the error number, the error description, and the error source to SubCloseApp.

In the program, here are the lines that accomplish the following (as listed above):

  • Check to see if we are running under WScript
    If(InStr(LCase(WScript.FullName),”wscript”)=0)ThenCallsubCheckScriptHost()
  • Create New Excel File
    Call subBuildXLS
  • Build and add the Header
    Call subAddLineXLS(GaryHeaders)
  • Add a line for each row of data
    For intCounter = 1 To intRows – 1
    GobjExcel.Cells(GintRow, _
    intCounter).Value = Trim(aryLineDetail(intCounter))
    Next
  • Create the footer
    CallsubFooter()
  • Save the file and clean things up
    If funSaveFiles(SAVE_FILE) Then
    MsgBox “Your inventory run is complete!”, vbInformation + vbOKOnly, REPORT_TITLE

Your file output will look like this:

MyDir 2

I tried to make the program as modular as possible so you can just drop sections of code into your programs or just modify things to suit your needs.

Disclaimer

—————————

Public Domain
This script is released into the public domain. You may use it freely. However, if you make any modifications and redistribute, please list your name and describe the changes under Revision History.
This script is distributed without any warranty, expressed or implied. If you choose to use this script, in whole or in part, you agree to take sole responsibility for any problems that may occur.

Provided “as is,” with no warranty whatsoever.
Copyright © PlumChoice 2014

—————————

Questions or comments? Contact Everon at 888-244-1748. Or email Frank Lindsey at flindsey@everonit.com.

Tech Tips for Techs: Bulk enabling Litigation Hold via Powershell

Standard

 

In this TechTip, we’ll discuss how to enable, tenant-wide, Litigation Hold via Powershell.

The reason for this post is two-fold: Microsoft doesn’t provide [as of the date that this was written] the ability to bulk-enable Litigation Hold via the MOP GUI. Secondly, there are some oddities that can occur in the 365 backend for people who have their on-prem Active Directory sync’ed with 365 – and this will show you how to get around the roadblocks that those oddities can sometimes present.

First, you’ll need to have Powershell setup and configured to connect to Office 365, as well as the credentials for a Global Administrator user. (Non-licensed is OK.) You will also need to know – in the number of days – how long you want Lit Hold to keep email. For this example I will use 7 years, or 2,555 days. Once you’re connected, run the following Powershell command:


Get-Mailbox | Where {$_.LitigationHoldEnabled -match "False"} | ForEach-Object {
$Identity = $_.SAMAccountName; Set-Mailbox -Identity $Identity -LitigationHoldEnabled $true -LitigationHoldDuration 2555
}

What this will do is parse through every mailbox object, filtering out the ones that already have Lit Hold enabled, and subsequently enabling it for the remainder.

* A key point here : Please note that I’m using the SAMAccountName parameter to identify the mailbox I want to work with. There are a couple of different params that the Set-Mailbox command will accept for Identity – the reason I chose SAMAccountName is because it’s guaranteed to be unique. In a DirSync’ed environment, if a user is “deleted” and then brought back with the same email address, that address can attach itself to multiple GUIDs behind the scenes. This can cause problems if you opt to use PrimarySMTPAddress for the Identity because it will match multiple GUIDs, resulting in the Set-Mailbox cmdlet not knowing which one to enable Lit Hold for. The resulting error in this situation will read: "The operation couldn't be performed because 'user@domain.com' matches multiple entries." Using SAMAccountName should prevent this problem from happening.

bank vault

Tech Tips for Techs: Converting a Shared Mailbox to a User Mailbox in Office 365

Standard

 

In this TechTip I’ll cover, in brief, how to convert a Shared Mailbox in Office 365 to a regular User Mailbox. This is, again, one of those things that Microsoft doesn’t provide the functionality for in the portal UI, but does allow you to do through Powershell. Like most Exchange-related tasks that you would leverage through PS, this also does not require a licensed user to run it – only Global Admin rights are required. You absolutely must ensure that you have a free license on your tenant to assign to the newly-created account, or the process will fail. (Cannot create a user mailbox on 365 without a license.)

Once you’re ready, fire up Powershell, connect as a Global Administrator, and run the following command:

Set-Mailbox user@domain.com -Type:Regular

 Once this command runs, go back to the Users and Groups section of the 365 portal and initiate a password reset against the newly-created account. Alternately, if you don’t want to go through the temporary password reset process, you can forcibly set the password if you’ve connected to the MSOL service by running this command:

Set-MsolUserPassword -UserPrincipalName user@domain.com -NewPassword YourPasswordHere -ForceChangePassword $False

That’s it! As part of the stored proc that runs on the backend, 365 should automatically assign an available license to the new user. NOTE: If you have more than one license type available, make sure that the new account has the license you want it to have. E3, E1, EXO1, etc.

 365 mailbox blog

Quick Tips: How to use “Out of Office” auto-reply on emails

Standard

 

Ever wanted to take a vacation but discover you missed tons of email delivered to you? Wouldn’t be nice if those coworkers would read their stupid daily reminder telling them you are going to be parasailing in Cozumel? Wouldn’t it just make too much sense if you could set up an auto responder so when they emailed you, they automatically got something along the lines of “Hey there, I’ll be out of the office until next Tuesday. Please reach out to (whoever) in my absence, or feel free to leave me a voice mail.” Well, you’re in luck, because this is a simple and easy setting to turn off and on, and it will save you a few headaches.

For Outlook 2010 and 2013:

  1. Click ‘File’ and then click ‘Info’ on the left side.
  2. Click ‘Automatic Replies (Out of Office).’
  3. Select ‘Send Automatic Replies.’
  4. You can change he time and date by selecting ‘Only send during this time range.’
  5. Where you see ‘Inside my organization,’ go ahead and type the message that you want to auto-reply. This will send an auto response to all people in your network, or coworkers who email you.
  6. Typing your message in the ‘Outside my organization’ will do the auto response for people outside your work (such as, say, a friend who is not your coworker, who decides to message your work email).
  7. OK.
  8. To turn this off, simply go into automatic replies and un-check the area to turn it off.
  9. That’s it!

2881878

For Outlook 2007:
  1. Open Outlook, click ‘Tools’ up on your tool bar and then click ‘Out of Office Assistant’ from the dropdown.
  2. Select ‘Send Out of Office Auto-Replies.’
  3. You can specify exact times of when to when by selecting ‘Only send during this time range,’ and you can even set the start and end times.
  4. Where you see ‘Inside my organization,’ go ahead and type the message that you want to auto-reply. This will send an auto response to all people in your network, or coworkers who email you.
  5. Typing your message in the ‘Outside my organization’ will do the auto response for people outside your work (such as, say, a friend who is not your coworker, who decides to message your work email).
  6. OK.
  7. To turn this off, simply go back into ‘Tools’ –> ‘Out of Office Assistant,’ and select ‘Do not send out of office auto-replies.’

2881879

 

For OWA (your email website you log into from your browser):
  1. Log into your OWA.
  2. Click ‘Options’ and then click ‘Out of Office Assistant.’
  3. To turn it on, click ‘Send Out of Office Auto-Replies.’
  4. You can also specify specific times of when to when here as well.
  5. Enter the message that you want people within your organization to see.
  6. For external people, or people who are not coworkers, type your message where it says ‘Send out of office auto-replies to external senders.’
  7. To turn it off, click ‘Do not send out of office auto-replies.’
  8. Save (always save!)

 

Bing, boom, bop, you will no longer have to come back with a sunburn and jet lag, only to spend 2 hours reading through tons of email, and then to remind the people in management that you’ve been gone for two weeks ;)

Excel Intermediate: Cleaning up formulas using Named Ranges

Standard

 

NOTE: All samples will be shown using Excel 2010. All examples and references will work in Excel 2003 – 2013, just the look and feel will be different.

One of the most difficult parts of understanding a complex Excel formula is determining what data is being referenced in the various ranges. For example, this formula seems more complex than it really is. You have no way of determining what is being compared in the IF statements without analyzing the ranges.

{=SUM(IF(Data!F2:F501='Dash Board'!C3,IF(Data!H2:H501>='Dash Board'!C4,IF(Data!I2:I501>='Dash Board'!C5,1,0),0),0))}

NOTE: This is an Array Function. If you are not familiar with them please read this blog.

All you can say for sure is that you are getting the total of rows on the Data page that meets three separate criteria.

Here is the same formula using Named Ranges:

{=SUM(IF(State=Project_State,IF(Employees>=Techs_Required,IF(Budget>=Project_Budget,1,0),0),0))}

That’s better. Now we know that we are testing the following criteria:

  • Is the State the same as the Project State?
  • Is the number of Employees greater than or equal to the Techs Required?
  • Is the Budget greater than or equal to the Project Budget?

All rows that meet these criteria will be counted.

Now let’s see how we set this up. In the upper left hand corner is a box that shows the current cell / range that is selected. This is called the Name Box.

Named Range 1

In the image above, you can see that cell E8 is currently active, and this is reflected in the Name Box. But did you know that you can change the Name of any cell or range of cells just by typing in the Name Box? Let’s give this a try. Click in cell C3 to make this the active Cell. Now click in the Name Box and type in “Project_State” (remember this is the name used in our second formula above). Once you press <Enter> the cell C3 will also be known as Project_State and can be referred to as such in any formula or reference.

Named Range 2

Now we need to create the references to cells C4 and C5 for our formula. Make cell C4 the active cell and name it “Techs_Required.” Finally, make cell C5 the active cell and name it “Projected_Budget” (the reason I gave it this name and not the name shown in B5 is because we will have another field named “Budget” on the Data tab).

That should cover everything on the “Dash Board” tab; now let’s work on the “Data” tab. There are two ways to name a range. Let’s try both methods.

First method:

Click in the cell A2 to make this the active cell. Now press <Ctrl><Shift><Down Arrow>. This will select all the cells under “First Name”. Your screen should look something like this:

Named Range 3

Now, in the Name Box type in “First_Name.” Once you press <Enter>, the range A2:A501 will also be known as Project_State and can be used in any formula or reference.

Second method:

Click in cell A1 to make this the active cell. Now press <Ctrl><Shift><End>. This will select the entire data table. Now, from the menu bar, select Formulas and click on Create from Selection.

Named Range 4

You will get a pop-up asking where the titles can be found. Select only Top Row.

Named Range 5

Ta-da! We now have named ranges for each column. To check this, press the dropdown arrow to the left of the Name Box.

Named Range 6

As you can see, all spaces in the title will be replaced with underscores (“_”).

So now we can create our formulas in a more sentence-like style. Here are a few examples that can be created quickly and will be easy to understand by anyone reading them.

  • =SUM(Budget)
  • =Countif(State, “=CA”)
  • =Average(Budget)
  • {=SUM(IF(State=Project_State,IF(Employees>=Techs_Required,IF(Budget>=Projected_Budget,1,0),0),0))}

A couple of thing to keep in mind about named ranges:

  • If a name already exists on the tab, it will automatically be overwritten without prompting
  • If a name already exists on another tab, you must specify the tab or it will default to the current tab’s name
    • For example Data!Budget will reference the range Budget on the Data tab
    • Adding new cells to the end of a range does not automatically extend the range
    • Inserting new cells in the middle of a range does automatically extend the range
    • Do not use reserved words when naming a range (select, if, sum…)

As you can see, using named ranges can not only make reviewing formulas easier, but you can also create new formulas more quickly.