Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    653
    Thanks
    93
    Thanked 14 Times in 13 Posts
    We have a spreadsheet that needs to be sent out to be filled in by a vendor. There are some columns with formulas and data that need to be hidden, but have to remain in the spreadsheet.

    Once I've hidden the columns, I selected the entire sheet, went to Format Cells>Protection and put a checkmark in Locked and Hidden. Then I protected the sheet with a password.

    But someone pointed out that they could open the protected sheet, select all, copy and paste to a new spreadsheet, and all the hidden areas can be unhidden.

    (I hid the columns by dragging the column headers. I also tried it by right clicking the column headers and selecting Hide).

    I also tried protecting the workbook, with a checkmark in both Structure and Windows. This did not accomplish the goal either.


    Is there a way to protect the sheet so that the recipient cannot see the hidden columns?

    Thanks

  2. #2
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As far as know, no. What you can do is create a second sheet, put your formulas in this sheet and then protect it with a password and as a last step you can hide it.
    This eco-post is made of recycled electrons

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 976 Times in 906 Posts
    Send them the blank form only, then when it comes back you can copy and paste into a spreadsheet with the formula?

    cheers, Paul

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I'd go with Paul's solution if you are really worried about security. Password protection for worksheet and workbook modification can be broken in about a minute with simple VBA.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    653
    Thanks
    93
    Thanked 14 Times in 13 Posts
    Thanks for the input.

    Copy and paste is not a desirable solution.

    This is a workbook with 16 sheets, and on each sheet there are about 16 cells, in non-contiguous locations, that the vendors need to fill in.

    And it is something that gets re-used quite a bit, not just this one time.

    Thanks

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Vincenzo,

    I'll make a couple of assumptions here.
    1. You don't want the vendors to see how you calculate values for competitive reasons.
    2. The data on the 16 sheets are in fixed never changing positions.
    3. The vendors do NOT need to see any of the surrounding data to fill in the information you require.

    If these assumptions are correct? I'd suggest that you create an input sheet that collects the data to be sent out to the vendors.
    Then write a macro which will automatically copy the data to the correct positions into the proper sheets in your workbook with the calculations. Since you only have to write the macro once and then just use it each time you get a data sheet in from one of the vendors you wouldn't be burdened with copy & paste and would be insured that the data always went into the proper sheet/cell locations. There are one or two other possibilities but this is the most straight forward and easiest to implement.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    As suggested by RG and PT, have the vendors submit an "Input" sheet to you. Have your main workbook's formulas refer to the "Input" sheet. No need for macros, copy and paste, etc. The formulas do the work.

  8. #8
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    653
    Thanks
    93
    Thanked 14 Times in 13 Posts
    Thanks for the replies.

    RetiredGeek, you are correct in your assumptions.

    Tim, I've done some experimenting just now using an Input workbook and a Main workbook. It works fine in my tests. But due to guidelines, I need to send the exact same Input Workbook (no file name changes) to up to 15 vendors, and then when I get the 15 filled in Input workbooks back, I need to create 15 Main workbooks, each one of those will be re-named with the vendor name. Will there be any issue with getting this to happen? I do not have the luxury of "pairing up" a particular Input Workbook with a specific Main Workbook for each of the 15 vendors.

    Thanks

  9. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Vincenzo - The 15 Main workbook approach should work. I suspect that there are much easier solutions.
    Can you give us an idea of what kind of info your "Input" sheet will be asking for?

    Tim

  10. #10
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    653
    Thanks
    93
    Thanked 14 Times in 13 Posts
    I've just taken a look and realized that my earlier post understated the number of entries. Each of the 16 tabs has about 30 cells that will have numerical entries (accounting formatted cells) and also about 30 cells that have text comments. These are non-contiguous locations.

    Thanks

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Vincenzo,

    If I read your post correctly? You are saying that you need to send out to the vendors a workbook containing 16 Tabs (worksheets) with data input cells scattered around these sheets. Is there any reason that you can't consolidate all the input values with appropriate headings & comments into a single sheet that would be sent out and then when you get it back you could use a macro that would create your main workbook from a template with all the formula, copy the input values to the main workbook on the appropriate sheets and then give the new workbook a name indicating the vendor who submitted the data. This is the approach I would take but I seem to hear in your last post that some guideline is keeping you from doing this?

    Sometimes, it pays in the long run to get the process changed. To do this you need to be able to convince the powers that be that making the changes will reap significant benefits (cost savings) down the road and for a long time to come.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    New Lounger
    Join Date
    Jan 2010
    Location
    Lilydale, Victoria, Australia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not sure that any macro is needed here. Just link your workbook to the input sheets.

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Vincenzo,

    Let me explain my idea further.

    I'd set up a workbook that contained only the macros which I needed and a selection routine to allow me to select one of the vendor's returned workbooks.

    I set up a workbook which I'd store as a template which contained all the formula.

    Then I'd rename each vendor workbook with the vendor's name and store all of them in a single directory.

    When opened the Macro workbook would immediately present you with a list of all the vendor files.
    When you select a file it would open the file then open the template.
    Copy all the vendor input information into the template.
    Save the template file to another directory renaming the file with the vendors name.

    By doing this you insure once the macros are programmed and tested that all operations are done consistently and you aren't hunting all over a workbook looking for the one link you didn't setup correctly.

    I know this is front loaded with some heavy work. However, if you do this a lot it will save you many many person-hours down the road and insure accuracy of the transfer and the final product.

    I hope this helps you work out your process.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    653
    Thanks
    93
    Thanked 14 Times in 13 Posts
    RG,
    Thanks for the suggestion. But I am only familiar with macros for very basic operations, and from the description it seems like there would be a learning curve involved that I do not have the time for right now.

    I think I will look into just using formulas to get info from an Input workbook that I'll create.

    Tim,
    you suggested there might be some easier solutions. Was an Input workbook what you were referring to?

    Thanks

  15. #15
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Tim Sullivan View Post
    Hello Vincenzo - The 15 Main workbook approach should work. I suspect that there are much easier solutions.
    Can you give us an idea of what kind of info your "Input" sheet will be asking for?

    Tim
    Hello Vincenzo - It seems like your workbook contains some non-public formulas that perform some action(s) on data supplied by your vendors. It would be helpful to know what kind of data you are asking your vendors for. It would also help to know what kind of things you are computing once you get the Vendor's responses. You don't have to tell us your formulas or any other proprietary info. We just need to get a hint of what data the vendor is giving you and what you want to do with it.

    A simple input sheet should be sufficient. Post a blank sample here when you finish it.

    You referred to 16 tabs in the workbook .... is it one tab for each vendor?

    I still think you can have some nice and easy solutions. We just need a little more info.

    Tim

Page 1 of 2 12 LastLast

Posting Permissions

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