Excel Drop Down From List in Different Workbook

To make it easier for people to enter data, you can create drop down lists on an Excel worksheet.

Usually the source lists are stored in the same workbook as the drop downs. However, with named ranges, it is possible to use a list in a different workbook.

In the screen shot shown below, the original list is in the workbook at the left. The drop downs are in a different workbook, on the right.

DataValListLinked

There Is a Catch

My preference would be to keep the lists and drop downs in the same workbook, but if you need to have them in separate files, this technique will allow you to do that.

There’s one catch though, when using this data validation technique. The source workbook, which contains the original list, must also be open, when you are using the drop down lists.

So, it’s not a perfect solution, but it’s fairly easy to implement, as long as you remember to open the other workbook too.

Excel 2010 Instructions

I’ve just uploaded a video with instructions for this technique in Excel 2010, so you can see the steps for creating the named ranges and data validation drop down lists.

The written instructions for Excel 2007 and Excel 2010 are in this blog post: Data Validation List From Different Workbook

________________

Excel Pivot Table from Multiple Sheets Update

If you have similar data on two or more worksheets, you might want to combine that data in a pivot table, to show the summarized results.

Unfortunately, the pivot table from data on multiple sheets can be a disappointment.

pivotmultipleconsol01

Create a Pivot Table with Programming

A couple of years ago, Excel MVP, Kirill Lapin (KL), shared a sample file that he created, with amendments by Hector Miguel Orozco Diaz.

It uses code to automatically create a pivot table from multiple sheets in a workbook.

You can read the details here: Create a Pivot Table from Multiple Sheets.

Revised Solution

Kirill’s sample file was created as a conceptual prototype, and targeted advanced VBA users. The code has minimal error handling and compatibility checks.

However, the sample file was extremely popular, and Excel users at all skill levels wanted to adopt this solution in their own applications. To make things easier, Kirill has created a similar solution based on ADO.

Advantages:

  1. No need for temporary file generation
  2. The code is faster and less prone to errors

Disadvantages:

  1. No manual refresh of the PivotTable
  2. Need to rebuild connection from the scratch to update the cache with new data

Download the ADO Sample File

You can download the new ADO version of the file from the Contextures website: PT0024 – Pivot Table from Multiple Sheets – ADO version.

There is also a “Plug and Play” version of the file, at the same link.
________________

Change Excel Comment Shape

When you insert a comment in Excel, a rather boring yellow rectangle appears, where you can add your text.

commentshapechange00

That’s all very proper and dignified, but sometimes you want something a bit more attention-getting.

commentshapechange01

In the good old days of Excel 2003, it was easy to change the comment shape, with a simple right-click. In Excel 2007 and Excel 2010, you need to add a command to the QAT, so you can change the comment shape.

Add the Change Shape Command to the QAT

  • At the right end of the QAT, click the drop down arrow
  • Click More Commands
  • In the Choose Commands From drop down, click All Commands

commentshapechange02

  • In the list of commands, click Change Shape, and click Add, to move it to the Quick Access Toolbar

commentshapechange03

  • Close the Excel Options window.

Change the Comment Shape

  • Right-click the cell which contains the comment.
  • Choose Edit Comment
  • Click on the border of the comment, to select it.

commentshapechange04

  • On the QAT, click the Change Shape command, and click on a shape to select it.

commentshapechange05

  • If necessary, drag the corner handle of the comment, to adjust its size, to fit the text.
  • When finished, click outside the comment.

Watch the Video

To see the steps for adding the Change Shape command to the QAT and changing the comment shape in Excel 2010 or Excel 2007, you can watch this short Excel video tutorial.

_____________________

Excel Christmas Planner

I’m just getting back from a vacation in South Carolina, where I didn’t use Excel too often, except to add up all my Christmas shopping expenses.

If you’re on my shopping list, you might be getting a basket from the Charleston market.

DSC_0211

Or maybe you’d like some of the local barbeque sauce or a bag of grits?

DSC_0208

Christmas Planner

There are only 10 days until Christmas, and I’m almost ready. How about you?

If you’re still planning and shopping, there is an Excel Christmas planner on the Contextures website, that you can use to help you stay organized.

The Excel Christmas planner has a budget sheet, where you can plan and track your spending.

image

Christmas Gift List

If you’re lucky, you don’t have too many gifts to buy. But even for a few gifts, it helps to make a list to keep track of costs.

The Excel Christmas planner helps you track your gifts too.

ChristmasGiftList

Christmas Tasks

The Excel Christmas planner has other sheets too, including a Christmas task list, so you can keep track of all those important things you’re supposed to do over the next few days.

You don’t want to realize on December 24th that no one has ordered the turkey!

ChristmasTaskList

Winter Weather

It was nice to spend some time in the warmer weather, and yes, it’s colder back here in Canada, but at least we don’t have to watch for alligators! Now I’d better go and order that turkey.

DSC_0161

_______________

Create Excel Chart With Shortcut Keys

To create a chart in Excel, you can select the chart data on the worksheet, then use the Ribbon commands to insert the chart. Or, for a quicker way, you can create an Excel chart with shortcut keys. See how to insert a chart, and change an Excel setting, so it inserts a specific chart type.

Continue reading “Create Excel Chart With Shortcut Keys”

Customize Excel Right-Click Menus

iconmenurighterDo you ever right-click on something in Excel, and the command that you wanted to use isn’t on that pop-up menu?

For example, if right-click on a cell, there is no command to turn off the gridlines.

menurighter01

If you can’t find the command on the right-click menu, you have to go to the Excel Ribbon or Quick Access Toolbar (QAT) instead, and try to find the command on one of the tabs there.

What commands would you add to a right-click menu? Are there commands that you added to the QAT, that would be even better in a popup menu?

Customize the Right-Click Menus

Fortunately, Doug Glancy has created a solution to the right-click menu problem, with his MenuRighter Add-in. With Doug’s free add-in, you can add commands to the pop-up menus, so the items that you need are easy to find.

The add-in is not for sissies! You need to know where the commands were on the old Excel 2003 menus – or be willing to poke around and find them.

Then, you select the right-click menu where you want to add the command, and click the buttons to add the command, and save the changes.

In the screen shot below, I have selected the ToggleGrid command from the Forms toolbar, and am adding it to the Cell popup menu, just above the Hyperlink command.

menurighter02

Find the Right Right Menu

There is a large collection of Targets, so it can be tricky to select the correct one. There are two Cell targets, so how can you decide which right menu is the right one?

While the MenuRighter is open, you can check the box for Show Labels on Menus. At the bottom of the list at the right, you can see the identity for the selected Cell target – 28-Cell.

If I right-click on cell A2, the popup menu also shows 28-Cell, so that confirms that I selected the Cell target that I want.

menurighter03

Use the Modified Right-Click Menus

After I added the Toggle Grid command to the Cell menu, I clicked Apply Changes, and closed the MenuRighter window.

Then, I can right-click on a cell, and turn the gridlines on or off.

menurighter04

It’s a command that I use frequently, so it’s very convenient to have it in the right-click menu. Thanks Doug, you made life in Excel a little easier!

Download the MenuRighter Add-in

For more instructions, and to download the MenuRighter add-in, you can visit Doug Glancy’s website: MenuRighter Add-in.

Watch the MenuRighter Video

To see the steps for adding a command to a right-click menu, by using the free MenuRighter add-in, you can watch this short Excel video tutorial.

___________________