On Monday, Excel MVP Kirill Lapin (aka KL) shared his macro to create a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook). I promised you a second pivot table macro, and here it is -- in today's example, Kirill combines data from a sales list and price list, stored in separate workbooks. The macro combines the data and calculates the selling price for each item, then creates a pivot table from the results.

Thanks, Kirill, for providing these two pivot table macros, and the powerful techniques they demonstrate for creating pivot tables from data in different locations.

The Report Workbook

Like the previous example, in this sample there is a main file named Report.xls, that contains Kirill's pivot table code, and a button that runs the macro. When you open the file, enable the macros, then click the Create Pivot button.

In the example, the orders are in the DB-Sales.xls file, and the price list is in the DB-Items.xls file.

After you click the button, the macro creates a pivot table from the two data files, stored in the same folder. You can refresh the pivot table, to show the latest data in the source files, as long as all the files stay in their original location.

The Union Query

Kirill's macro creates a Union query to combine the data from all the selected files and calculate the sales price. In the Connection Properties dialog box, you can see the SQL string for the Union query, with the Units * Unit Price calculation.

Download the Sample File

To see Kirill's pivot table code, you can download the Pivot Table Union Calculation example. The zipped folder that contains the Report.xls file, and the two data files. Unzip the folder, and keep all the files in the same folder. When you open the Report.xls file, enable macros to run the code.

Excel Tweet of the Day

Have you ever had a project like this one?

This extensive Excel spreadsheet and four days of research has led me to a surprising conclusion: I really don't care.

For more entertaining and enlightening Excel tweets, culled from the thousands posted every day, see Excel Theatre Blog.

______________

If you want to create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table. However, that creates a pivot table with limited features and functionality. Last year, Excel MVP Kirill Lapin (aka KL) shared his brilliant code to create a Union query and build a fully functional pivot table from data on different worksheets.

Now, Kirill is back to share two more pivot table macros, and I'm sure you'll be impressed by both of them. In today's example, he's written a macro to create a pivot table from multiple Excel files. This is based on a solution that Kirill posted in one of Russian-speaking Excel forums for generating a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook).

The Report Workbook

The main file is named Report.xls, and it contains Kirill's pivot table code, and a button that runs the macro. When you open the file, enable the macros, then click the Create Pivot button.

The Data Files

When you run Kirill's macro, it prompts you to select one or more data files, all stored in the same folder. In this example, the files for Alberta, Ontrio and Yukon are selected.

All of the data files must have the same structure, and the macro works with the data stored on Sheet1 in each file. In the screen shot below you can see Sheet1 in the Alberta and Ontario files, which have identical column headings.

The Pivot Table

After you select the files and click Open, the macro creates a pivot table based on the data from Sheet1 in each of the selected files. Because the pivot table is based on a Union query, and not built from Multiple Consolidation Ranges, it's a fully functioning pivot table, and you can pivot, group and filter the data, as usual.

You can also refresh the pivot table, to show the latest data in the source files, as long as those files stay in their original location.

The Union Query

Kirill's macro creates a Union query to combine the data from all the selected files. If you open Microsoft Query, you can see the SQL string for the Union query, and all the data from the selected workbooks.

Download the Sample File

To see Kirill's pivot table code, you can download the Pivot Workbooks example. The zipped folder that contains the Report.xls file, and the five sample data files. Unzip the folder, and keep all the files in the same folder. When you open the Report.xls file, enable macros to run the code.

Pivot Table Macro #2

Come back on Wednesday to see Kirill's second pivot table macro. It's another creative twist on creating a pivot table from data in different workbooks.

______________

If you've added comments to an Excel worksheet, you have a couple of built-in options for printing the comments.

• Show the comments on the worksheet, and print them as displayed.
• Print the list of comments at the end of the worksheet, on a separate printed page.

Printing the comments on the worksheet is okay if there are only a couple of comments, and you can arrange them so they don't cover the data.

For more than a couple of comments, the list at the end of the worksheet is a better choice. However, with the built-in list printing option, you just get the cell address and comment, printed in a long, single column.

Create Your Own List of Comments

Instead of using the built-in list of printed comments, you can use a macro to create your own list of comments on a separate worksheet, and print that list. It's also a great way to review all the comments on a worksheet, and use sorting or filtering to focus on specific comments.

Shown below is the Excel VBA code to create a list of comments from the active sheet, written by Dave Peterson. For more comment programming examples, including Dave's code to list all the comments in the entire workbook, see Excel Comments VBA.

The Comment List Code

The ShowComments macro adds a new sheet to the workbook, and lists all the comments, the comment author name, and the comment cell's value, address and name (if any). At the end of the macro, the first row is formatted in bold font, and the column widths are autofit.

Sub ShowComments()
'posted by Dave Peterson
    Application.ScreenUpdating = False

    Dim commrange As Range
    Dim mycell As Range
    Dim curwks As Worksheet
    Dim newwks As Worksheet
    Dim i As Long

    Set curwks = ActiveSheet

    On Error Resume Next
    Set commrange = curwks.Cells _
        .SpecialCells(xlCellTypeComments)
    On Error GoTo 0

    If commrange Is Nothing Then
       MsgBox "no comments found"
       Exit Sub
    End If

    Set newwks = Worksheets.Add

     newwks.Range("A1:E1").Value = _
         Array("Address", "Name", "Value", "Author", "Comment")

    i = 1
    For Each mycell In commrange
       With newwks
         i = i + 1
         On Error Resume Next
         .Cells(i, 1).Value = mycell.Address
         .Cells(i, 2).Value = mycell.Name.Name
         .Cells(i, 3).Value = mycell.Value
         .Cells(i, 4).Value = mycell.Comment.Author
         .Cells(i, 5).Value = mycell.Comment.Text
       End With
    Next mycell

    With newwks
        .Rows(1).Font.Bold = True
        .Cells.EntireColumn.AutoFit
    End With

    Application.ScreenUpdating = True

End Sub

_____________

Last week, I posted Bob Ryan's Excel macro for formatting a pivot table in Classic style. Bob's macro formats the first pivot table indexed on the active sheet.

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)

Ideally, you'd only have one pivot table on a worksheet, to prevent problems with overlapping, and Bob's code would work very well. However, as you know, life in Excel isn't always ideal! Let's look at a few scenarios, and how to modify the macro to deal with them.

Select a Pivot Table

In the blog post comments, Yard suggested a variation on the code, so the macro would run on the selected pivot table, to accommodate worksheets with multiple pivot tables. If a cell in a pivot table isn't selected, an "Oops" message would be displayed.

    On Error Resume Next
    Set PT = ActiveCell.PivotCell.PivotTable
    On Error GoTo 0

    If PT Is Nothing Then
        MsgBox "No PivotTable selected", vbInformation, "Oops..."
        Exit Sub
    End If

Thanks, Yard, for your sample code. On a multiple pivot table sheet, the user can control which pivot table is formatted.

Format All Pivot Tables on Active Sheet

Taking that idea a bit further, let's assume you have a worksheet with several pivot table on it. With Yard's code, shown above, you could select a cell in one of those pivot tables, and run the macro to format that pivot table only.

But, what if you wanted to format all the pivot tables on that sheet? It would take a while to select each pivot table, and run the macro. Instead, you could modify the code, so it formats all the pivot tables on the active sheet.

    For Each PT in ActiveSheet.PivotTables
        'the formatting code goes here
    Next PT

Format All Pivot Tables on All Worksheets

Finally, what can you do if there's more than one worksheet with pivot tables? You don't want to waste time selecting each worksheet, and running the macro to format all the pivot tables on that sheet.

To loop through the worksheet, you could modify the code, so it formats all the pivot tables on each worksheet in the active workbook.

    Dim ws as Worksheet
    For Each ws In ActiveWorkbook.Worksheets

        For Each PT in ws.PivotTables
            'the formatting code goes here
        Next PT

    Next ws

______________

If you're filling in an order form in Excel, you can use the VLOOKUP function to find the selling price for each item in the sales order. For example, in the screen shot below, the order form is on the Orders worksheet, and a VLOOKUP formula in column D pulls the cost from a pricing table on the Prices worksheet.

Lookup Range in Another Workbook

However, you might want to keep your price list separate from the orders workbook, so the prices are easy to update. If your price list is in a different workbook, you can still use a VLOOKUP formula to pull the data, by referring to the external list. To refer to another workbook in a VLOOKUP formula, follow these steps.

1. Open both workbooks (this step isn't mandatory, but makes it easier to create the link)
2. Create the VLOOKUP formula, and for the table_array argument, select the lookup range in the other workbook. In the screen shot below, the lookup table is in the PriceList.xlsx workbook, in a range named PriceLU.

Closing the Lookup Workbook

While the price list lookup table workbook is open, the VLOOKUP formula will show the workbook name and the referenced range address or range name. You can see the PriceLU name and workbook information in the formula bar, in the screen shot below.

If the price list lookup table workbook is closed, the VLOOKUP formula will still work, and the full file path for the price list lookup table workbook will be shown in the formula.

Watch the Video

To see the steps for referring to a VLOOKUP range in another workbook, please watch this short Excel tutorial video.

_____________

It's easy to protect a worksheet in Excel, but it's not so obvious how you leave some of the cells unprotected, so users can make changes to those cells. You can follow this tutorial to learn how to do that, and maybe you'll even see the weird dialog box heading that I show below.

For example, on a data entry sheet, you might have some cells with formulas, that you don't want users to mess up. However, you want the users to be able to enter the date, item name and quantity for an order.

In the screenshot below, the selected cells -- A2:C6 -- should be unprotected, so users can enter data. The rest of the cells should be protected, so the formulas cannot be changed or deleted.

Step 1: Unlock the Data Entry Cells

By default, all the cells on the worksheet will be locked, if you protect the worksheet. To allow changes to some cells, you have to change the protection setting for those cells.

1. On the worksheet that you're going to protect, select the cells that users will be able to change -- cells A2:C6 in this example.
2. On the keyboard, press Ctrl + 1 to open the Format Cells dialog box.
3. On the Protection tab, remove the check mark from Locked.
4. Click OK, to close the dialog box

Note: In the screenshot above, you can see that the dialog box name shows as "Custom Lists", even though it's the Format Cells dialog box. It seems to appear if you open the Format Cells dialog box, go to the Protection tab, and then close the dialog box. The next time you press Ctrl+1, the Custom Lists heading is on the Format Cells dialog box.

Step 2: Protect the Worksheet

1. Go to the worksheet that you're going to protect.
2. On the Excel Ribbon, click the Review tab
3. Click Protect Sheet.

Watch the Video

To see the steps for allowing changes in some cells on a protected Excel worksheet, please watch this short Excel video tutorial.

Allow Users to Change Cells in Protected Sheet

_________________

Are there files that Excel opens automatically, every time you start Excel? Something changed in your computer, and Excel files are opening automatically, and you want to get rid of them. Keep reading, to see where those files might be located, and how to stop them from opening.

Also, remember to enter the Excel Giveaway at Simply Learning Excel, for a chance to win a 1-hour online Excel session (transferable) or an Excel book.

Stop files from opening automatically in Excel 2007 and Excel 2010

To stop files that automatically open in Excel 2007 and Excel 2010, you can remove a folder name in the Excel options:

• Click the Office Button, then click Excel Options (In Excel 2010, click the File tab, then click Options)
• Click the Advanced category, and scroll down to the General section.
• In the box for 'At startup, open all files in', you might see the name of a folder, and its path
• Clear the folder information from that box (or go to that folder and remove the unwanted files).
• Click OK, to close the Excel Options dialog box.

Check the XLSTARTUP Folders

If there was nothing listed in the Startup box, the unwanted files might have been accidentally saved to one of the XLSTART folders.

• C:\Program Files\Microsoft Office\Office12\XLSTART   (for Excel 2007)
• C:\Program Files\Microsoft Office\Office14\XLSTART   (for Excel 2010)
• C:\Documents and Settings\User name\Application Data\Microsoft\Excel\XLSTART

In Windows Explorer, go to those folders, and remove the unwanted files, to prevent them from opening automatically when Excel starts.

Stop files from opening automatically in Excel 2003 and earlier versions

For earlier versions of Excel, there are instructions in the Contextures Excel FAQs: When I start Excel, why do a million files open up automatically?

More Ways to Prevent Files from Opening

You can also use a command-line switch, or the Esc key, to stop files from opening automatically. For more details on how to block files from opening automatically, see the Microsoft Knowledge Base article: How to prevent files from opening automatically in Excel.

For help with troubleshooting other problems when starting Excel, see Jan Karel Pieterse's article: Startup Problems.

Excel Tweet of the Day

Do you really trust the numbers in every report you read? This guy might work for you!

My pivot table won't pivot. Screw it. I'll make the numbers up.

For more entertaining and enlightening Excel tweets, culled from the thousands posted every day, see Excel Theatre Blog.

_______________

My friend and client, Bob Ryan, from Simply Learning Excel, has just published a hands-on, no fluff, Excel book -- Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less. To celebrate the book launch, I asked Bob to share one of his favourite Excel tips with you, and you can read it below.

Bob is also having an Excel Giveaway on his Simply Learning Excel blog this week, and the details of that are also listed below. It's a great giveaway, even if you already know the Excel essentials. The top prize a personalized 1-hour online Excel session with Bob, and it's transferable! So, if your co-worker (or relative) is driving you crazy with Excel questions, you can send them to Bob for help. Sweet!

And now, here's Bob…

Macro to Establish Classic PivotTable Settings, Plus…

Some time ago, I was getting ready to train a group of people about PivotTables. As I was documenting the steps, I started feeling more and more annoyed at the number of steps it took to create the kind of PivotTable I typically use. So, I wrote a macro to automate the steps. (I also submitted a suggestion to Microsoft to allow users to create a customized standard/default PivotTable, but I don’t see it in Excel 2010.)

I wanted to share this macro, but since my website is generally geared to folks who don’t know about or need macros (yet), I asked Debra if I could be a guest writer, and she kindly agreed. A final note: While I appreciate Debra’s willingness to share this information on her site, the content really belongs to her because most of this information came from her books, website, and/or her personally. I hope you find this useful.

What the Macro Does

Once you insert a PivotTable and enter a field(s) into the Values area, the code does the following to PivotTable(1) on the active sheet:

1. Applies the Classic PivotTable display, with gridlines and no colors (I like this so I can Copy the PivotTable and Paste Special Values and Formatting.);
2. Ensures that only data that still exists in the data that drives the PivotTable will appear in the PivotTable dropdown lists.
3. Sets all fields to ascending order with no subtotals, including fields that are not in the Row Labels or Column Labels areas, and;
4. For the data field(s) in the Values area, changes the setting to Sum, changes the number format, and, if the field in the Values area is named “Amount” or “Total Amount” it shortens the label in the PivotTable to “Sum Amt” or “Sum TtlAmt” respectively.

Download the Sample File

You can view the pivot table formatting macro code, and download Bob's Format Pivot Table Macro sample file. The file is zipped, and in Excel 2007 format. Because it contains a macro, you'll have to enable macros when you open the file.

A "Simple" Giveaway

The Giveaway Prizes

• The top prize, with a value of approximately $145, is a copy of the book Simply Learning Excel 2007 and a live, personal, one-hour Excel session with Bob via WebEx. Bob will cover and/or answer any questions about any topic from the book, e.g. PivotTables, Filters, VLOOKUP, etc. Bob will contact the winner to arrange a mutually convenient time, and also find out what the winner would like to cover. This prize is transferrable, so the winner can give it to a family member or co-worker, who needs some extra help, or they could even share it with a few co-workers, in a meeting room.
• There will be four runners-up, and each one will win a free book, with a value of approximately $20 each. They, along with any reader of the book, can get their questions answered at no extra cost via a live WebEx session by submitting their questions to Ask the Author… LIVE!™ at www.SimplyLearningExcel.com.

How to Enter

Simply submit your answer to this question at http://simplylearningexcel.com/blog/2010/08/15/a-simple-giveaway/:

• In all the years (or days) that you have known Excel, what is the most valuable thing – feature, formula, etc – you have learned about Excel,

and/or

• What is the one thing about Excel that you have shared with others that they have valued most? Was it PivotTables? Keyboard shortcuts? Spreadsheet design? Macros? Please let us know.

The Giveaway Rules

• All responses must be submitted to Bob’s blog at http://simplylearningexcel.com/blog/2010/08/15/a-simple-giveaway/.
• The entry deadline is 12:00 noon (Eastern time zone) on Monday, August 23rd, 2010.
• One entry per person – any additional entries will be deleted from the draw, with one exception. If you answer both parts of the question, you will be entered twice.
• A random draw will select the winner from all valid entries.
• The winner will be notified by email, so please provide a valid email address. This will not be publicly visible.
• The winner will be announced on Bob’s blog on Tuesday, August 24th. Good Luck!

Watch the Video

To see how much time you can save by using a macro to format a pivot table, watch this video. It took me a couple of minutes to manually format the Excel pivot table, and change some of the pivot table options, and just a couple of seconds to do all the same steps with Bob's macro.

Note: If you record your own pivot table formatting macro, follow Bob's example to add variables, so the macro works on any pivot table, no matter what the field names are, or where it's located.

About the Author

Robert Ryan, MBA, CPA is a long-time passionate user of Excel, the author of "Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less," a unique step-by step book designed for basic to intermediate users, and the host of "Ask the Author… LIVE!™" where Bob answers questions from readers of his book in live WebEx sessions at no extra cost. For more information, please visit Bob’s website at http://SimplyLearningExcel.com.

_________________

This week, there were a couple of Excel conditional formatting questions in the blog comments. Ron asked about changing the font colour for the highest, second highest and lowest values. Guido wants to highlight values that aren't multiples of another cell's value.

I'll answer the questions here, so they're easier to find. Maybe you've encountered similar conditional formatting problems, and this will help.

Change Font Colour for Top 3 Values

The first question is from Ron, who wants to change the font colour, instead of the fill colour:

I am trying conditional format a range of three cells where the FONT of the highest value in the range will be in Red follow by Blue and Green. I know it can be done using cell fill but, with a large spread sheet, it will look like dog's breakfast. Any help would be appreciated. Also can this be done across worksheets (i.e. three different worksheet in the same file)

Ron didn't say that the three cells were adjacent, in the same row, but I made that assumption in my example, shown below.

To find the highest, second highest, and lowest values for cells C2:E2 with a worksheet formula, you could do the following:

• To find the highest value, use MAX  =MAX($C2:$E2)
• To find the second highest value, use LARGE =LARGE($C2:$E2,2)
• To find the lowest value, use MIN =MIN($C2:$E2)

The column references are absolute, so that all 3 cells in the row will refer to the same range.

You can use the same formulas in conditional formatting, compared to the active cell, and add the font formatting for each formula.

Format the Highest Value

To set up the conditional formatting for the highest value:

• Select cells C2:E6, with C2 as the active cell
• On the Excel Ribbon's Home tab, click Conditional Formatting, and click New Rule
• In the New Formatting Rule dialog box, under 'Select a Rule Type', click 'Use a formula to determine which cells to format'
• In the formula box, type the MAX formula:   =C2 = MAX($C2:$E2) The first reference to C2 is relative, so each cell will check its value compared to the MAX in the $C2:$E2 range.
• Click Format, and on the Font tab, select Red as the font colour, then click OK, twice, to close the dialog boxes.

Now the highest values are highlighted in each row.

Format the Second Highest Value

To set up the conditional formatting for the second highest value:

• Select cells C2:E6, with C2 as the active cell
• On the Excel Ribbon's Home tab, click Conditional Formatting, and click New Rule
• In the New Formatting Rule dialog box, under 'Select a Rule Type', click 'Use a formula to determine which cells to format'
• In the formula box, type the LARGE formula:   =C2 = LARGE($C2:$E2,2)
• Click Format, and on the Font tab, select Blue as the font colour, then click OK, twice, to close the dialog boxes.

Format the Lowest Value

To set up the conditional formatting for the second highest value:

• Select cells C2:E6, with C2 as the active cell
• On the Excel Ribbon's Home tab, click Conditional Formatting, and click New Rule
• In the New Formatting Rule dialog box, under 'Select a Rule Type', click 'Use a formula to determine which cells to format'
• In the formula box, type the MIN formula:   =C2 = MIN($C2:$E2)
• Click Format, and on the Font tab, select Green as the font colour, then click OK, twice, to close the dialog boxes.

Now the highest value in each row is highlighted in red, the second highest is blue, and the lowest is green.

Conditional Formatting From Another Worksheet

To answer the second part of Ron's question -- conditional formatting won't let you refer to cells on a different worksheet, or in a different workbook. However, you can refer to a workbook level named range that's on a different worksheet, but that wouldn't help in the example shown above.

For example, you could highlight all the cells on Sheet1 that are higher than the maximum allowed, if the maximum is on Sheet2, in a range named MaxAmt.

=C2>MaxAmt

And here's another example of referring to a named range in conditional formatting.

Highlight Cells That Are Not a Multiple of Another Cell

The next conditional formatting question came from Guido:

need conditional formatting based on the multiple of the number oin another cell. How to do that?

For ex: in the formatted cell can only be the multiple of the number in the other cell, otherwise it colours for ex red

To check this on the worksheet, you could use the MOD function: =MOD(D2,C2)

The MOD function returns the remainder after a number is divided by divisor. If the result is zero, then D2 is a multiple of C2.

Highlight the Non-Multiples

Shown below is the sample data for this conditional formatting example.

To add the conditional formatting, and highlight the non-multiples in column D:

• Select cells D2:D6, with D2 as the active cell
• On the Excel Ribbon's Home tab, click Conditional Formatting, and click New Rule
• In the New Formatting Rule dialog box, under 'Select a Rule Type', click 'Use a formula to determine which cells to format'
• In the formula box, type the MOD formula:   =MOD(D2,C2) <> 0
• Click Format, and on the Fill tab, select Red, then click OK, twice, to close the dialog boxes.

Now the non-multiples in column D highlighted in red.

More Excel Conditional Formatting Examples

There are more Excel conditional formatting examples on the Contextures website.

______________

In Monday's blog, you saw how to make simple dependent data validation drop down lists. After creating the drop downs, you added some flexibility by using the IF function in the data validation formula.

Today you'll see a couple of problems that can occur when you refer to other cells in your data validation, and those cells are blank.

Invalid Entries Are Allowed

If you create a data validation formula that refers to another cell, and that cell is empty, users might be able to type invalid entries in the cell. For example, in the screen shot below, Buffalo was entered in cell B4. As you can see in the list in cell B5, that isn't one of the cities allowed when the adjacent cell in column A is empty.

Change the Ignore Blank Setting

To prevent people from entering invalid data when the cell referred to is empty, you can open the Data Validation dialog box, and remove the check mark from the Ignore Blank setting.

With the Ignore Blank setting turned off, users will see an error message if they try to enter invalid data.

Problem with Circle Invalid Data

I always turn off the Ignore Blank setting when using dependent data validation drop down lists, as I described above. However, last week I heard from Paul, who uses the Circle Invalid Data feature in one of his workbooks.

When the Ignore Blank setting is turned off, Excel treats empty cells as invalid data, when you run the Circle Invalid Data feature. That's a helpful feature when you don't want to allow empty cells, but not very helpful in this case.

To remove the circles, use the Clear Validation Circles command on the Excel Ribbon's Data tab.

Make a Choice

Despite extensive experiments, I couldn't find a formula that would prevent invalid entries in a dependent data validation cell, where the referenced cell is empty, without turning off the Ignore Blank setting. If you find one, please let me know, and I'll update this blog post.

In the meantime, I'd rather prevent invalid entries, than catch them later, so I'll stick with that setting change. I don't really use the Circle Invalid Data feature anyway, so I won't miss it!

Watch the Video

To see the steps for turning off the Ignore Blank setting, and the problems that can occur, please watch this Ignore Blank Problems in Excel Data Validation video.

_______________