I have a spreadsheet template with some quite complex formulas created with considerable help from Paul Grzelak.

Complex? Try this on for size:

=IF(NOT(ISERROR(SEARCH("FLEX[",W13))),MID(W13,SEARCH("FLEX[",W13)+5,SEARCH("]",W13)-(SEARCH("FLEX[",W13)+5)), IF(AG13<>0,(ROUNDUP((INT((E13-A13+1)/7)/AG13),0)*(P13+Q13+R13+S13+T13+U13+V13)) + C113,(ROUNDUP(INT((E13-A13+1)/7),0)*(P13+Q13+R13+S13+T13+U13+V13)) + C113))



The spreadsheet is used by the sales staff to create order forms for subsequent data entry, and it works perfectly.

Unfortunately, it doesn't work the way I want.

I want to keep the master template, write protected, on a network drive that is accessible by all the sales staff. This is not a problem. Since the file is write protected, if they want to save their work, they simply do a "Save As". This IS a problem, because the "Save As" default location is to tne network drive where the template resides, and I need to have the saved files go to each salesperson's local hard drive, in a directory named: C:\_MediaStar.

That's not the worst of it, though. I could solve that problem with some extra work on my part by putting a copy of the master template in the C:\_MediaStar directory in each of their computers. The big problem is that when the Excel program opens the master template pointed to by the desktop shortcut, if the user wants to use the File/Open command in excel to go to a previously saved order file, the default directory is always "My Documents". So they have to browse up three or four levels, then go back down a different branch of the tree to find their saved files.

I had thought the "Start In" setting in the Shortcut properties determined the default locations for the files, but it apparently does not.

I was told that the only way to do this was to put visual basic macros in the spreadsheet, but that is not acceptable, because once that happens, every time you open the spreadsheet you have to go through a dialog box or two explaining how dangerous the macros are and do you want to enable or disable them. I just wanna click on the icon and open the spreadsheet, and have immediate access to my previously saved files, and be able to save new files to the same location.

Is this too much to ask? Am I overlooking something obvious here?

tanstaafl.

Firstly, the default file location for Excel (at least in Excel XP) is in Tools | Options | General | Default file location.

Secondly, you're going about this the wrong way:

If this Excel spreadsheet is a "template", then you should save it as an XLT file (not an XLS file), and put it in the C:\Documents and Settings\user_name\Application Data\Microsoft\Templates directory.

Theoretically, there's a way to specify a location for shared templates, but I can't find it in Office XP.

Then your template will appear under File | New, and the user will be encouraged to save their XLS copy to a different location.

I was told that the only way to do this was to put visual basic macros in the spreadsheet, but that is not acceptable, because once that happens, every time you open the spreadsheet you have to go through a dialog box or two explaining how dangerous the macros are and do you want to enable or disable them.

I think you can get around this by digitally signing your project and have your users click the (one-time) "Always trust content from tanstaafl". Searching Office Help for "digital certificate" will guide you in the right direction here.

Google search yields this, YMMV:

Office XP - you have to set the workgroup templates location within Word and
this setting applies to ALL Office XP programs. Means you have to put Word,
Excel, whatever templates in the same location but that's not a big deal -
our issue was that we had previously had separate folders for Word, Excel
etc so Excel was looking in the Word folder...