Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What I have:

An Excel workbook that uses a VB macro to calculate the price of a product based on a complicated Bill of Material set of instructions.

How the workbook is designed:

The workbook has two worksheets: one with material costs and constants and the other with the product configuration. There is a button that when clicked, calculates the prices of a specific product configuration using the costs and constants in the other worksheet.

What I would like to design:

I want to be able to have a centrally-hosted Excel file/page where our sales reps can enter the product configuration, click a button and get the price of a product. However, the sales reps must NOT have access to the constants and costing page - this is a key constraint.

Any suggestions?

share|improve this question

This sounds like what the Excel Services part of Microsoft SharePoint was designed for.

share|improve this answer
maybe he does not have that.. – soandos Aug 30 '11 at 20:27
It's true that you might not have it, and it's not free. =) I'm sure if there's a free alternative that doesn't take too long to set up, someone will suggest it. – rakslice Aug 31 '11 at 19:27

You need to create an addin containing the constraints and constants page.

As an addin it will be hidden.

Note, that if they know Excel trickery they may be able to unhide it, but you may (not tested) be able to put a password on it. You can also do more Excel tricks like put all the constants in white font, but there's no perfect system if it can't be locked.

As far as hosting goes, then you'll have to bring in some other program like SharePoint. Vanilla Excel wasn't designed for this.

share|improve this answer

If you provide a template they use for the product configuration, a passworded sheet would work, but if security is a big issue, why not make this a webform that outputs an excel file?

You can keep all your calculations totally private, distribute what the salesman needs easily and still in a format, that ultimately didn't require you to worry about exposing the costing.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .