Software

Office challenge: How can you force Excel to open a specific workbook?

Here's the answer to last week's challenge on omitting 0 values from an averaging expression, along with a new question to test your Excel skills.
Many of us use Excel a lot, but we work with only one or two workbooks. After launching Excel, we open the same workbook we use everyday and go to work. Can you omit the repetitious open task and force Excel to open a specific workbook when it launches?

Last week we asked…

“How can you get Excel to return an average that doesn't consider 0 values?" The responses were all sound and valuable. You guys really know Excel! Most of you shared the most common solution, a combination of the SUM() and COUNTIF() functions, in the following form: =SUM(range)/COUNTIF(range,"<>0") Joshua.Masson@... Offered an interesting array solution: =AVERAGE(IF(range<>0,range,"")) Gordon.cooper@... pointed out that the array accounts for blank cells, whereas the more traditional SUM() expression doesn't. He's right, but I agree with gbentley@... in this particular case. Neither expression is superior, but it is important that you know how both expressions respond to blanks and text (any value that doesn't specifically match the COUNTIF() function's criteria). A few of you mentioned Excel 2007's new AVERAGEIF() function. Interestingly, if you exclude 0 values, this function also ignores blank cells -- curious. Thanks for playing! It's interesting to see so many different solutions to the same question.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
mhbaum
mhbaum

Personally I use the RUN Command. Type in the full path of the file and then OK. This will open the program and the file. 3 Notes. 1. This works with any file. 2. You can use a file utility [I use Free Commander and the Alt+Ins command on the Edit Tab] to make it easier to copy and paste the full path. 3. The path will remain in your recently open drop down as long as you do not clear out.

eastbayri
eastbayri

Save the file into the XLStart folder, which is created when Excel is installed. In Windows XP, this folder is located at C:\Documents and Settings\[user]\Application Data\Microsoft\Excel\XLSTART. Anytime you open Excel, it automatically opens all files in that folder.

Gordon Or-8
Gordon Or-8

Use the Tools - Options as PiGuy states. You can then open one or many files from the single folder set in the option. (You could als0 use an Autoexec macro in the personal.xls workbook - which always opens)

mihamil
mihamil

Any workbook placed in the following directory will open automatically. C:\Program Files\Microsoft Office\Office12\XLSTART Replace "Office12" with whichever you use. Also if you create a workbook that is hidden you can use macros from that workbook on any others that are open. Place the macro workbook in that directory and your macros will always be there for you to use.

PiGuy314
PiGuy314

Not sure where you are supposed to post the answer but you can configure excel to open all files in a given directory in the options: Tools Menu > Options > General Tab > "At startup, open all files in:" This is for Excel 2003.

harttl
harttl

Recent documents on the start menu or recent documents from the Office button.

Broosbee
Broosbee

Oh, I like that and had never thought of it. Thanks. :)

nikki.e.vaughan
nikki.e.vaughan

I just have a shortcut to my file and open that, as opposed to opening Excel, and clicking File>Open> and finding my file.