Brainstorming about linking a file that never leaves our servers to a file we send out

So I have a workbook and it has a LAYOUT tab, where you make product selections.

Behind the scenes, the selections you make on this tab do many things: codes are built, pricing is calculated on a QUOTES tab, etc.

The selections you make on the LAYOUT tab are driven by data validation lists on a TABLES tab.

Here's my problem...

We want to send this LAYOUT tab out to customers for them to fill in. We want them to have only the table options have provided on our TABLES page.

However, we don't want to be sending out the whole file because we don't trust Excel's password security, and we don't want our customers figuring out how to get into our pricing.

What solutions can you recommend? I am trying to keep it as simple as possible, I am hoping to avoid people having to copy and paste the customer LAYOUT onto the network internal LAYOUT. In a perfect world, we wouldn't need to maintain a customer AND internal version of the file.

• Is there some kind of "extraction" program/third party option to simplify this?
• Is there a simple macro solution?
• Maybe you have experience with this type of thing and can give some advice?

Well as long as were dealing in the Abstract world (lack of a concrete example), I would prepare a Customer Template, have them send back the template and have a macro upload the info from Customer template. Customer template has 2 tabs: 1) to fill in 2) Data tab which 'pulls' the info off of the 1st tab allowing you to Copy (macro?) into your file (network). Might want to add a Control number so you don't duplicate orders?

Originally Posted by queuesef

Well as long as were dealing in the Abstract world (lack of a concrete example), I would prepare a Customer Template, have them send back the template and have a macro upload the info from Customer template. Customer template has 2 tabs: 1) to fill in 2) Data tab which 'pulls' the info off of the 1st tab allowing you to Copy (macro?) into your file (network). Might want to add a Control number so you don't duplicate orders?

I guess I would have them copy the customer layout "tab" to their personal copy, then use a macro to copy and paste the data. Hmmm. I wish I could think of a better solution, but I can't.

Thus the problem with spreadsheets vs web sites. What you describe would be ideally implemented as a web site front-end for your customers, with all the underlying logic safely behind the scenes on your server.

If you only expect your customers to fill out an order spreadsheet which they fill out using nothing but data validation drop-down lists, then they send those order spreadsheets back to you and you load their selections into your full model, then it should be straightforward to load entries from those customer order spreadsheets into your full model. Maybe even as simple as using nothing but external reference formulas like ='[Current Customer.xlsx]LAYOUT'!X99 in your core model's LAYOUT!X99 cell. Just copy a give customer's spreadsheet as Current Customer.xlsx, open it, then open your core model. Your core model should update with the entries from Current Customer.xlsx.