Managing Version Control in Excel using Macros

VERSION

If you work with spreadsheets that are regularly being updated and amended by multiple users then you will know something about version control. If you then add to this mix, the need to periodically distribute the updated spreadsheet for public viewing you will be more than well aware that Microsoft Excel isn’t really designed to handle version control very well.

I’m currently working with NHS contract proposals for the new financial year. The models are complex and the figures involved are high. We are up to version 20 already and the first cut hasn’t even been published. Between each issued version I know I will be inundated with emails asking what has changed and why.

I’m getting twitchy just thinking about it and I’m already dreaming about version control.

I’ve put together a few macros to enforce some rules for version control and to maintain an audit trail of changes and versions.

Methodology for Controlling Versions in Microsoft Excel
My spreadsheet includes tabs of data related to patient care, but it will work for any model that you wish to maintain a version trail for.

You need a tab for documenting the changes between each version. Mine is called Version Control but then I’m an accountant and don’t really do imagination.
On this sheet are two named ranges, VERSION and FILENAME (in B7 and B9 respectively) and two macro buttons for running the save options.

When the document is saved I want the user to overwrite the master document so that it always shows the latest version and I want them to save the file to a version folder and increment the filename to the next version number. So each save process creates one new file and overwrites another.

To ensure this happens just as I want, I need to prevent users from saving the spreadsheet in their own way. I do this by writing a procedure in the workbook object that will run whenever they attempt to save the document.

1
2
3
4
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox "When you are happy with the changes, please go to the VERSION CONTROL tab and freeze the document to save"
End Sub

This directs the user back to the Version Control tab where they can either perform a FREEZE save or a temporary save. The temporary save is for convenience in case large changes are being made, it will save a single version with a temporary filename. The FREEZE save creates a new version and overwrites the master document, it also requires the user to enter some notes relating to the changes made.

Here’s the code for the FREEZE save:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
Sub FreezeWorkbook()
'Working in Excel 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim wsVC As Worksheet
Dim Destwb As Workbook
Dim VersionFilePath As String
Dim VersionFileName As String
Dim MasterFilePath As String
Dim MasterFileName As String
Dim VersionDate As String
Dim Version As String
Dim CurrentVersion As String
Dim iLastRowVC As Long 'LAST ROW IN VERSION CONTROL SHEET
Dim Author As String
Dim Changes As String
Dim AmendRef As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'UPDATE THE VERSION CONTROL DETAILS
ActiveSheet.Unprotect
Set wsVC = Worksheets("VERSION CONTROL")
iLastRowVC = wsVC.Cells(Rows.Count, "B").End(xlUp).Row

'Current Version
CurrentVersion = wsVC.Range("a" & iLastRowVC).Value

'Insert version
Version = InputBox("Please enter the next incremental Version Number (Current Version = " & CurrentVersion & ")")
wsVC.Range("a" & iLastRowVC + 1).Value = Version

'Insert version date
VersionDate = Format(Now, "dd-mm-yy hh-mm-ss")
wsVC.Range("B" & iLastRowVC + 1).Value = VersionDate

Author = InputBox("Please enter your name")
wsVC.Range("c" & iLastRowVC + 1) = Author

Changes = InputBox("Please enter a brief description of changes made")
wsVC.Range("d" & iLastRowVC + 1) = Changes

AmendRef = InputBox("Enter the ref to the Amendments Document or N/A if none available")
wsVC.Range("e" & iLastRowVC + 1) = AmendRef

wsVC.Range("F" & iLastRowVC + 1).Value = Range("HLACTIVITY") 'Returns a the total activity value from a named range within the spreadsheet
wsVC.Range("G" & iLastRowVC + 1).Value = Range("HLVALUE") 'Returns a the total financial value from a named range within the spreadsheet

'Save the master workbook
Application.DisplayAlerts = False
Set Sourcewb = ActiveWorkbook

'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

'Overwrite the master report or create it if it doesn't yet exist
MasterFilePath = "c:\My Documents\2010-11 A&F Contract Templates\Masters\" 'Amend to suit your filing system
MasterFileName = Range("FILENAME") & " Master"

Range("VERSION").FormulaR1C1 = Version 'Overwrite the version number on the version control sheet

With Sourcewb
.SaveAs MasterFilePath & MasterFileName, FileFormat:=FileFormatNum, WriteResPassword:="" 'This ensures that the master is not password protected otherwise I get problems overwriting the file
End With
Application.DisplayAlerts = True

'save the version workbook
Set Sourcewb = ActiveWorkbook
'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

'Save the new workbook and close it
VersionFilePath = "c:\My Documents\A2010-11 A&F Contract Templates\Versions\RAP\"

VersionFileName = Range("FILENAME") & " " & Version
ActiveSheet.Protect

With Sourcewb
.SaveAs VersionFilePath & VersionFileName & FileExtStr, FileFormat:=FileFormatNum, WriteResPassword:="password" 'this sets password protection in place for the version spreadsheets
End With

MsgBox "New version saved as " & VersionFilePath & VersionFileName & " and Master Updated"

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

and the Temporary Save code which saves the file in the versions folder with a “Temp” suffix:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub TempSave()
'Working in Excel 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim TempVersion As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

ActiveSheet.Unprotect
TempFilePath = "c:\My Documents\2010-11 A&F Contract Templates\Versions\"
TempVersion = Format(Now, "yy-mm-dd hh-mm-ss")
TempFileName = "Temp " & Range("FILENAME") & " " & TempVersion
Range("VERSION").FormulaR1C1 = TempVersion
ActiveSheet.Protect

With Sourcewb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
End With

MsgBox "New version saved as " & TempFilePath & TempFileName

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

I’ve used the plugin Syntax Higlighter to display the VBA code. It looks great but unfortunately it doesn’t copy and paste well. You can download a working spreadsheet that includes the modules above if you want to copy them – Excel Version Control Example V1 the password to open is NCL.

As usual I have botched this macro together from many hints and tips found elsewhere on the interweb, this page from Ron de Bruin was particularly useful.

I think I need to add some loops into the input box code because users can choose not to enter any comments or an appropriate version number at the moment and I have no means of controlling the errors.

I’ve also had to run this without any password protection on the master file. If I password protect it the macro fails with a write protection error. I’m not sure if I ought to be able to get around this or whether it is not actually possible to save over a write protected file. Dunno.

Excel Pivot Table or CrossTab to Flat List

flatlist1

Excel to CrossTabI often find myself struggling to deal with data that has been rendered almost unusable by the data provider who has converted it to a cross tab format. Usually they think they are helping and have probably started with a nice flat list then spent ages formatting it so the data spreads out across the columns. Sometimes they have achieved this by putting the data into a pivot table but then have pasted the table as values and removed all links to the underlying data. Very helpful.

Yesterday I had to deal with a data file that looked a little like this only  it spread out across 455 columns and was frankly useless.

 

I wrote a post a while back that demonstrated how to convert a simple cross tab back to a manageable data list but I wanted to expand this with a slightly more complex example which had more field headings.

So starting with the table above which was probably a pasted copy from a pivot table I applied a little bit of formatting to achieve the following starting table.

What I have done is remove the grouping that the pivot table applies and ensured that I have the relevant  week number, project and department details against each name

I often wish to convert pivot table outputs back into a data file and the way I fill in the blanks caused by grouping data is to prepare a sheet with formulas that copy values from above (or the side as appropriate):

I then copy the pivot table data and then use the paste special command selecting paste values and skip blanks as the options.

This results in the following output, which I then copy and paste over itself as values, to give me the desired structure.

 

Having spent a bit of time on the structure of my starting file I can run the macro that will work through each row and transpose the data from a columnar layout to one based on rows.

The advantage of this format is that I can now create my own pivot tables and cut the data as I see fit to produce multiple summary formats according to my audience.

 

Here’s the macro I use:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
Sub CrossTabToList()

Dim wsCrossTab As Worksheet
Dim wsList As Worksheet
Dim iLastCol As Long
Dim iLastRow As Long
Dim iLastRowList As Long
Dim rngCTab As Range 'Used for range in Sheet1 cross tab sheet
Dim rngList As Range 'Destination range for the list
Dim ROW As Long

Set wsCrossTab = Worksheets("Sheet1") 'AMEND TO SHOW SHEET NUMBER WITH THE CROSS TAB
Set wsList = Worksheets.Add

'Find the last row in Sheet1 with the cross tab
iLastRow = wsCrossTab.Cells(Rows.Count, "A").End(xlUp).ROW

Set the initial value for the row in the destination worksheet
iLastRowList = 2

Find the last column in Sheet1 with the cross tab
iLastCol = wsCrossTab.Range("A2").End(xlToRight).Column

'SET THE HEADING TITLES IN THE LIST SHEET
wsList.Range("A1:F1") = Array("NAME", "PROJECT", "TYPE", "PLAN/ACTUAL", "WEEK", "HOURS")

'Start looping through the cross tab data

For ROW = 3 To iLastRow 'START AT ROW 3 AS THIS IS WHERE DATA BEGINS
Set rngCTab = wsCrossTab.Range("A" & ROW, "C" & ROW)'initial value A3 SETS THE RANGE TO

'INCLUDE ALL STATIC DATA - IN THIS CASE NAME, PROJECT, TYPE
Set rngList = wsList.Range("A" & iLastRowList) 'initial value A3

'Copy individual names in Col A (A3 initially) into as many rows as there are data columns
'in the cross tab (less 3 for Col A-C).
rngCTab.Copy rngList.Resize(iLastCol - 3)

'SELECT THE HEADING ROW WITH FORECAST/ACTUAL
'Move up ROW (INITIALLY 3) rows less TWO and across 3 columns (using offset function). Copy.
rngCTab.Offset(-(ROW - 2), 3).Resize(, iLastCol - 3).Copy

'Paste transpose to columns in the list sheet alongside the static data
rngList.Offset(0, 3).PasteSpecial Transpose:=True

'SELECT THE ROW WITH THE WEEK NUMBERS
'Move up ROW (INITIALLY 3) rows less ONE and across 3 columns (using offset function). Copy.
rngCTab.Offset(-(ROW - 1), 3).Resize(, iLastCol - 3).Copy

'Paste transpose to columns in the list sheet alongside the static data
rngList.Offset(0, 4).PasteSpecial Transpose:=True

'Staying on same row (3 initially) copy the data from the cross tab
rngCTab.Offset(, 3).Resize(, iLastCol - 3).Copy

'Past transpose as column in list sheet
rngList.Offset(0, 5).PasteSpecial Transpose:=True

'Set the new last row in list sheet to be just below the last name copied
iLastRowList = iLastRowList + (iLastCol - 3)

'increment ROW by 1
Next ROW

End Sub

I’m afraid the VBA code isn’t rendering very well – you’ll have to replace all the ” and ‘ marks before the macro editor will recognise the code.
Here is a copy of file – you could copy the macro code from here: Excel to CrossTab.xls

Excel CrossTab Table to Flat List

I had a query on my sumproduct tutorial that was effectively asking if I could work backwards from an excel crosstab or data table to get back to the simple flat list.

Converting a table like this:

sumproduct

Back to the data sheet like this:

sumproduct

If you didn’t have to do this sort of task very often you could get by with a pivot table solution and a few stages of formatting but it would only work if your data values were numbers that could be summed in the pivot.

If you need to convert Excel data tables (or crosstabs) into flat lists on a regular basis then you will need a macro.

I have prepared a workbook that demonstrates both the pivot table and macro solution and you can download it from here:
ExcelTabletoFlatList.xls

The following macro does the trick and utilises the VBA I introduced in my last post on consolidating worksheets, to determine the last used row and last used column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
Sub CrossTabToList()

Dim wsCrossTab As Worksheet
Dim wsList As Worksheet
Dim iLastCol As Long
Dim iLastRow As Long
Dim iLastRowList As Long
Dim rngCTab As Range 'Used for range in Sheet1 cross tab sheet
Dim rngList As Range 'Destination range for the list
Dim I As Long

Set wsCrossTab = Worksheets("Sheet1")
Set wsList = Worksheets.Add

'Find the last row in Sheet1 with the cross tab
iLastRow = wsCrossTab.Cells(Rows.Count, "A").End(xlUp).Row

'Set the initial value for the row in the destination worksheet
iLastRowList = 2

'Find the last column in Sheet1 with the cross tab
iLastCol = wsCrossTab.Range("A1").End(xlToRight).Column

'Create a new sheet and set the heading titles
wsList.Range("A1:C1") = Array("NAME", "GRADE", "VALUE")

'Start looping through the cross tab data

For I = 2 To iLastRow

Set rngCTab = wsCrossTab.Range("A" & I) 'initial value A2
Set rngList = wsList.Range("A" & iLastRowList) 'initial value A2

'Copy individual names in Col A (A2 initially) into as many rows as there are data columns
'in the cross tab (less 1 for Col A).
rngCTab.Copy rngList.Resize(iLastCol - 1)

'Move up a I rows less one and across one column (using offset function) to select heading row. Copy.
rngCTab.Offset(-(I - 1), 1).Resize(, iLastCol - 1).Copy

'Paste transpose to columns in the list sheet alongside the names
rngList.Offset(0, 1).PasteSpecial Transpose:=True

'Staying on same row (2 initially) copy the data from the cross tab
rngCTab.Offset(, 1).Resize(, iLastCol - 1).Copy

'Past transpose as column in list sheet
rngList.Offset(0, 2).PasteSpecial Transpose:=True

'Set the new last row in list sheet to be just below the last name copied
iLastRowList = iLastRowList + (iLastCol - 1)

'increment I by 1
Next I

End Sub

Macros to Consolidate Worksheets

I’ve got a spreadsheet with tabs for each individual directorate. Within the tabs are tables with the same layout but different sizes (number of rows).

I want to group together all the used data on each tab and create a single consolidated sheet of continuous data.

Obviously I need a macro to copy and paste my data from each sheet but the tricky bit is selecting the relevant (or used) area in each sheet.

On my search around the interweb I found some really useful snippets of VBA code on the Ozgrid website which enable you to determine the last used row according to a number of different criteria and assign this to a variable such as “iLastRow”.

1
2
'find row before next blank cell in column A
iLastRow = Range("A1").End(xlDown).Row
1
2
'find last used row in column A
iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
1
2
'find last used row on sheet
iLastRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _SearchDirection:=xlPrevious).Row

It’s a simple step then to use this variable to define a range of used cells.

1
Range("A1:P" & iLastRow).Select

I’ve used the above techniques to consolidate my worksheets and here’s a copy of my working code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Sub ConsolidateSheetsforSubForm()
' ConsolidateSheetsforSubForm Macro
' Copies used subform data in individual sheets and pastes into one sheet as continuous data

Sheets("Ap Table Sub Form").Select

'find last used row column a of Ap Table Sub Form sheet
ilastrowsubf = Cells(Rows.Count, "a").End(xlUp).Row

' Clear the existing data in the subform sheet
'specify location in subform to clear data
Range("a5:k" & ilastrowsubf).ClearContents

' Start selecting the data from Sheet S&M (LY)
Sheets("S&M (LY)").Select

' find last used row in column I
ilastrowsm = Cells(Rows.Count, "i").End(xlUp).Row

' use ilastrowsm variable to define range to copy in S&M sheet
Range("i4:s" & ilastrowsm).Copy

' specify location in subform to paste data
Sheets("Ap Table Sub Form").Select
Range("a5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("K&R (BV)").Select

' find last used row in column I of K&R sheet
ilastrowkr = Cells(Rows.Count, "i").End(xlUp).Row

' use ilastrowkr variable to define range to copy in K&R sheet
Range("i4:s" & ilastrowkr).Copy
Sheets("Ap Table Sub Form").Select

' find first blank row in column a of Ap Table Sub Form sheet
ifirstblankrowsubf = (Cells(Rows.Count, "a").End(xlUp).Row) + 1

' specify location in subform to paste data
Range("a" & ifirstblankrowsubf).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("OVERHEADS").Select

' find last used row in column I of OVERHEADS sheet
ilastrowover = Cells(Rows.Count, "i").End(xlUp).Row

' use ilastrowover variable to define range to copy in OVERHEADS sheet
Range("i4:s" & ilastrowover).Copy
Sheets("Ap Table Sub Form").Select

' find first blank row in column a of Ap Table Sub Form sheet
ifirstblankrowsubf = (Cells(Rows.Count, "a").End(xlUp).Row) + 1

' specify location in subform to paste data
Range("a" & ifirstblankrowsubf).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A5").Select
End Sub

You can do similar things in order to find the last column as well:

1
2
3
4
5
6
7
8
9
'find column before next blank cell in row 1
iLastColumn = Range("A1").End(xlToRight).Column

'find last used column in row 1
iLastColumn = Cells(Columns.Count, "a").End(xlToLeft).Column

'find last used column on sheet
iLastColumn = Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Vlookup Errors – The Third Way

I’ve just published a post detailing a method of removing the errors returned by vlookup or hlookup utilising conditional formatting.

I had previously been using the double vlookup method to replace errors with blanks or zeros but then discovered that these excessive lookup functions had a tendency to bloat my spreadsheets.

While the conditional format method works, I can’t say that I particularly like it so I’ve been on the hunt for another method.

My first double lookup error hiding method looked like this:

=IF(ISERROR(VLOOKUP(L$7&$C13,’[WTE Apportionment Tables.xls]WTE APP TABLES SUB F’!$B:$K,9,0)),””,VLOOKUP(L$7&$C13,’[WTE Apportionment Tables.xls]WTE APP TABLES SUB F’!$B:$K,9,0))

and here’s method no 3 that feels somewhat neater:

=IF([WTE Apportionment Tables.xls]WTE APP TABLES SUB F’!$B:$K,L$7&$C13),VLOOKUP(L$7&$C13,’[WTE Apportionment Tables.xls]WTE APP TABLES SUB F’!$B:$K,9,0))

That’s a working formula but it is a bit confusing, so here’s a simpler example the syntax:

=IF(COUNTIF(A1:A10,”Some Value”),VLOOKUP(“Some Value”,A1:B10,2,FALSE),0)

The COUNTIF function is a logical function and will return TRUE or FALSE depending on whether “Some Value” is found in the range A1:A10. The IF function follows the format =IF(logical test, value if true, value if false). So if the COUNTIF function is TRUE the VLOOKUP will run, if it is FALSE the value 0 is returned.

Spreadsheet Efficiency: VLookup and Conditional Formatting to Remove Errors

excel

I’ve started working at a new hospital and it seems to be plagued by unwieldy spreadsheets that have a tendency to crawl through saves and then sometimes refuse to open. They are riddled with links and lookups to external spreadsheets and the whole thing feels fairly precarious.

I’ve been reading a bit about good spreadsheet design with a view to whittling down the complexity and discovered that lookup functions are particularly demanding on the system.

Now I’m quite a sinner when it comes to the use of the double lookup function to remove errors such as #N/A and #DIV/0!

Here’s an example of such a formula that returns a blank if the first lookup function results in an error:

=IF(ISERROR(VLOOKUP(L$7&$C13,'[WTE Apportionment Tables.xls]WTE APP TABLES SUB F’!$B:$K,9,0)),””,VLOOKUP(L$7&$C13,'[WTE Apportionment Tables.xls]WTE APP TABLES SUB F’!$B:$K,9,0))

It works fine, but it doubles up the number of lookups that Excel needs to run through and for a large spreadsheet this can really slow down the performance.

It seems that conditional formatting is a suitable alternative but one that I’ve always ignored because I didn’t know how to structure the condition to work for errors.

I do now though and here’s how:

excel

1. Select the range you wish to apply the format to.
2. Goto Format – Conditional Formatting
3. Select Formula Is in the drop down box
4. Type the formula =iserror(ref) where ref is the reference to the first cell selected in the range
5. Hit Format and select the font colour to be white
6. OK – finished the job

Conditional Format 2

My current spreadsheet hasn’t grown that large yet but nevertheless it had 7200 of the double vlookups in 18 columns, by removing the unnecessary error catching lookup I dropped the file size by 22%.

As the errors weren’t actually removed, I had to use an array formula to get the totals for the column but that was included in the space saving above.

The array formula for summing a range with error values:

{=SUM(IF(ISNUMBER(H12:H418),(H12:H418)))}

NB. The curly brackets are achieved by entering the formula using CONTROL, SHIFT, ENTER.

Adding an Image to the Header or Footer of Every Sheet

From Excel 2002 onwards you can enter an image, probably a logo, into the header or footer of your worksheet. This code enables you to create a macro that will automate the addition of the logo to every sheet in your workbook.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub MultipleHeader()
'created 10/01/2008 by Angela Wolff
Application.ScreenUpdating = False 'stops the flashing
  Dim WS_Count As Integer
   Dim I As Integer

   ' Set WS_Count equal to the number of worksheets in the active workbook.
  WS_Count = ActiveWorkbook.Worksheets.Count
   
   ' Begin loop.
  For I = 1 To WS_Count
      ActiveWorkbook.Worksheets(I).Activate
      ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
        "C:\Document\hhnt_007528.jpg"  'obviously change this to the image and path of your desire.
   ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = "&G"
    End With
   Next I
End Sub

NB. The With End With code is only really required if you are going to change a number of parameters at once.

Scroll Bar Chart

series-chart.jpg

This is a very neat advanced Microsoft Excel technique that enables the spreadsheet user to alter the visible range displayed. This can be achieved by defining the start point within the spreadsheet data series and also the number of data points to display in the Excel spreadsheet chart.

All the examples are available to examine in the Microsoft Excel example spreadsheet: Scroll Bar Chart

scroll-pic-a.jpg

In this example I have a full years data displayed in the worksheet range $A1:$C53.

scroll-pic-b.jpg
We need to create the two slide bars on the Excel worksheet.

Open the forms toolbar within Excel and select the scroll bar option and then drag out the form of a horizontal bar. You can move this around to finalise both size and positioning.

Right click over the selected scroll bar and choose Format Control.
In the box called Cell link write $L$19. This is the cell in which the incremental value of the slide bar will be recorded.
Repeat the process by drawing another scroll bar and assign its cell link to $L$21.

If you now move the arrows either side of the two scroll bars you will see that the values in the corresponding cell link will change. These will form the values for the height of our displayed values and the starting point respectively, and as such they will appear in our offset function defining the y values. To make these offset formulas easier to read I suggest we name the cell links.

To add a name to a specific cell or area – from the Insert menu, choose Name and then Define or use the keyboard shortcut CTRL F3. In the “Names in workbook” box type in a descriptive name for your cell, in this case I have used ZoomValue.
In the “Refers to” box we simply type $L$19.

Similarly for ScrollValue the name is defined as:

scroll-pic-c.jpg

We need to set a dynamic range for the x and y values. So, taking the y values as our base we can use the offset function to define the values to be plotted.

The OFFSET function is in the following format:

ref,rows,cols,height,width.

Choosing $C$2 as our reference cell, we now require a measure of our starting point (given by our SCROLL slide bar), the cols section = 0, the height relates to the number of rows of data to be charted and is returned by our ZOOM slide bar and the width = 1.

So define the named range =

OFFSETSheet1!$C$2,ScrollValue,0,ZoomValue,1)

The x values or labels can simply be referenced from the y values:
scroll-pic-d.jpg

When you create the graph you need to the dynamic ranges for the x and y values, so in this example the formulas would be:

x axis values = Sheet1!XValues
y axis values = Sheet1!YValues

xval-chart.jpg

series-chart.jpg

This is your dynamic chart completed. Try the arrows on both scroll bars to see how they alter the display.

You can hide your data table so long as you remember to change the chart options so that hidden cells are plotted and the cell links can be formatted so they too are not visible.

See the Microsoft Excel spreadsheet example Scroll Bar Chart.

Dynamic Chart

This Microsoft Excel tutorial, shows an example of how to create a dynamic chart by moving the charting range as data is added to the spreadsheet.
Here we start with monthly data for Retailer A, showing Petcare sales complete up to July.

pic-a.jpg

A standard Excel line chart of this data would be shown thus:

pic-b.jpg

To reduce the displayed area to the existing data within the actual column, while allowing it to expand automatically as more months figures are added, we use defined names.

Insert menu, choose Name and then Define or use the keyboard shortcut CTRL F3.

In the “Names in workbook” box type in a descriptive name for your chart data, in this case I have used ChartActual.
In the “Refers to” box we use the offset function to define the data within column C.

pic-c.jpg

The offset function is written in the following format:

=OFFSET(Ref,rows,column,height,width)

The COUNTA function is the vital part that makes the range responsive to the number of cell entries in the Actual column. This function returns the number of non-blank cells in a range. It is written in the height section of the OFFSET function and therefore determines the number of rows that will make up the name ChartActual.

In this example the reference is $C$1 which corresponds to the “Actuals” title, the resulting area starts 1 row down and 0 columns across. The number of rows corresponds to the number of entries in column C less 1 for the title entry and the total column width is 1.

The same procedure is now used to define names for the ChartLabels and ChartTargets.

pic-d.jpg

The OFFSET function is again used, this time referencing the newly defined ChartActuals. So ChartTarget starts on the same row as ChartActuals but is 1 column to the left of it. When the height and width component of the OFFSET function is not stated the height and width will be the same as the reference.

pic-e.jpg

The final stage involves changing the graphs data source.
Right click within the graph and choose Source Data.

pic-f.jpg

Select Actual in the Series box and in the Values box type in the newly defined name, in this case Sheet1!ChartActuals.
Perform the same action for Target and use Sheet1!ChartLabels to define the Category (X) axis labels.
Click OK and the chart should now look like this but is in addition fully responsive to new entries.

pic-g.jpg

Sumproduct

All the worksheets (and some extras) shown in the microsoft excel tutorial below are included in the example spreadsheet, available to download Sumproduct.xls

This is quite a special excel spreadsheet function but the online help available from microsoft is unfortunately pretty poor.

It works as an advanced form of the SUMIF formula where you can sum a range of values based on a number of different criteria.

Here is the simple formula layout or syntax:

=SUMPRODUCT((Condition 1)*(The range to sum))

or slightly more complex:

=SUMPRODUCT((Condition 1)*(Condition 2)*(The range to sum)

the “*” symbol between conditions represents an AND a “+” symbol would represent an OR.

Here is a simple example, of a schools course grades displayed in the following spreadsheet table,
sumproduct

You will probably want to summarise this to show the total number of students passing the course in each grade.

sumproduct

This makes use of the simple SUMPRODUCT formula in cell F2:

=SUMPRODUCT(($B$2:$B$15=E2)*($C$2:$C$15))

the use of absolute referencing here enables the formula to be copied all the way down the table.

In English the spreadsheet formula reads:

Sum the results in column C where the values in column B = “A”

Moving on to a slightly more complex example, we could increase the summary table to show each students performance across the different grades.

sumproduct

This requires the addition of another condition – namely STUDENT equals.

The spreadsheet formula in cell I2, which can again be copied across is:

=SUMPRODUCT(($B$2:$B$15=I$1)*($A$2:$A$15=$H2)*($C$2:$C$15))

In English the spreadsheet formula reads:

Sum the results in column C where the grades = “A” and where the student = “Adam”

To see these powerful excel functions in action and utilising the Condition 1 OR condition 2 terminology, download my example microsoft excel spreadsheet.