+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    04-25-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    72

    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?

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

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

    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?

  3. #3
    Registered User
    Join Date
    04-25-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    72

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

    Quote Originally Posted by queuesef View Post
    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.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 04-18-2017, 10:19 PM
  2. Replies: 0
    Last Post: 01-15-2017, 06:54 PM
  3. Trimming Bulleted list from .docx file leaves symbols
    By cworkman123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-10-2015, 12:52 PM
  4. vba to send data from multiple user excel file to one file collated
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2014, 11:08 PM
  5. Excel 2010 file with VBA save and send code works-but file can't be found.
    By carlton.clay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-13-2013, 09:46 AM
  6. Replies: 0
    Last Post: 04-13-2006, 05:10 PM
  7. Brainstorming possible file errors/problems
    By Nick Hebb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-30-2005, 08:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1