Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding Formulas In Shared WorkSheets

    We have created numerous worksheets that we use to calculate quotations for a wide variety of products and customers. In the past, we have printed the worksheet and then faxed it as price quotation to our customer. Now many customers are asking to receive the quotations via email. How can we send our worksheets as email attachments, but not expose our pricing formulas?

    Thanks in advance. I really appreciate everyone who shares their hard earned knowledge and experience on this forum.

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Formulas In Shared WorkSheets

    For safety/security, we always convert to .pdf and send those.
    Otherwise, I would create a macro to open a new workbook/template and copy the values you want to it, then invoke Outlook or whatever. (No macros and no formulas in the new workbook.)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Formulas In Shared WorkSheets

    What I would do would be to go to each sheet in the workbook, Select the entire sheet (the little blank square in the upper left corner), Do and Edit Copy, then Do an Edit Paste Special Values. That should replace all of the formulas on the sheet with the calculated results. Save the workbook with a new name and send it to the client. If you have a lot of sheets, and/or do this very often, you will probably want to create a macro to do the copy and pasting.
    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hiding Formulas In Shared WorkSheets

    I would not consider it wise to send any client a spreadsheet. It is too easy to modify, even accidently.

    If the data you want to send will fit on a page, or a series of pages in Word, you could use the copy picture feature and paste the resultant image into a word document.

    To achieve this, in Excel, holding the shift key, select Edit, and you have an option to Copy Picture. Select that and go your word document and Paste Special, and select picture. You should then have an image of your sheet in Word - no formulas, and difficult to edit. It can also be resized to suit the page.

    If you do want to send the excel sheet, use the protection feature.
    Make sure each cell is both locked and hidden, apply a good password and the formulas will not be visible, and the data can not be changed. However, passwords can be cracked.

    Andrew C.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Hiding Formulas In Shared WorkSheets

    I often use a technique related to Legares'. And I have the "Paste values" icon on the toolbar, so it's a three step operation; select all, copy, paste values.

    Within the Workbook create TWO sheets with the quote info you want to issue; you may end up with sheets named "quote copy", "quote calcs", "product details", "brilliant calculations", "secret tables", "labor rates", etc., where "quote copy" is identical to "quote calcs" (reason will be apparent later).

    I usually attach the entire file to the e-mail, copy paste values the entire "quote copy" sheet, delete all the other sheets. If you are a little more paranoid, edit to values and "quote copy" only sheet first, save "customer price calc" workbook as "customer quote" workbook, so you have both, then attach the quote only.

    Now, if you later have to vary the quote, all you do retrieve the detailed pricing calcs, adjust, copy the new "quote calcs" sheet and paste its' values onto the "quote copy" sheet, save and remail. (Reissue in haste, repent at leisure. It happens to me all the time.)

    A case where convenience breeds redundancy.

    Don't forget to round your numbers on the quote sheet. One major customer of mine gets very childish about quotes whose decimals go below pennies. Now "he" gets rounded up all the time.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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