Hiding columns in Excel

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Send them the blank form only, then when it comes back you can copy and paste into a spreadsheet with the formula?

cheers, Paul

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!
Computer Specs

Reply | Quote

Not sure that any macro is needed here. Just link your workbook to the input sheets.

Reply | Quote

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!
Computer Specs

Reply | Quote

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

Reply | Quote

Each vendor gets their own worksheet. The worksheet has 16 tabs. They enter prices on about 15 different options on each tab.

Then the calculations are pretty straightforward, mostly multiplication of the dollar amounts that they enter by another fractional discount amount that they enter, then that number is multiplied by 36 and the result displayed.
The text comments that they enter would just need to be copied.

So the input sheet would have dollar amounts, decimal multipliers, and text.

Thanks

Reply | Quote

I’ve attached a very simplified example of what one tab of the input sheet might look like.

Thanks

Reply | Quote

Hello Vincenzo – Thank you for the sample. Can your Vendors fill out a single sheet that has all the items info on one sheet? I have attached an example Vendor input.xlsx that includes 3 different items (widgets, small widgets, large widgets). I used different prices and discount rates for each item.

You would copy that single sheet into the Input Tab on the Vendor Master.xlsx. All the vendor input will be copied in a single step.
The other tabs of Master would perform all of the calcs. (See Tab 1, Tab2, Tab3)

Hope this helps.

Reply | Quote

Hi Tim,

That is an interesting approach that I had not considered.

I’m not sure if putting all the input fields on a single tab would work, since sometimes blocks of cells would need to be deleted when they are not relevant, and that would leave areas of blank cells on the sheet (presently we just delete the unnecessary tabs). But on the other hand your format does in some ways simplify it for the vendors.

I need to give it some thought and talk about it with my partner.

Thanks for your ideas and for taking the time to create those sheets.

Vince

Reply | Quote

Hi Vince – As we learn more about what is needed, it is easier to propose solutions.
On your current Vendor workbook, you mentioned 16 tabs of calculations. Is each tab making calculations for one different “item name” per tab? The 16 tabs make the same calcs for 16 different “item names” ? If the 16 tabs make the same calculations, can you supply a sample of the info that is calculated on a couple of the tabs? There is no need to include any private info.

I am sure we can come up with a Vendor Input sheet where the vendor only fills in data relevant to that particular Vendor.

Tim

Reply | Quote

All 16 tabs are filled in by each vendor. Each tab is for a different style widget.

When I mentioned that sometimes a tab is not relevant and is removed, it is because we are not requesting pricing on that model of widget. And while all the tabs are basically the same and doing identical calculations, some tabs have additional options available so have a few added lines. But all the calculations are of the types that you already have on your example.

Thanks

Vince

Reply | Quote

Hi Vince – Have you had a chance to show the previous files to your partner?

I have attached a revised Vendor Input Workbook with 2 additional alternatives for Input sheets (see Sheet2 and Sheet3). Does either one look easier for the vendor’s to use? The Master could use simple formulas like the previous Master or use some “Lookup” formulas for ease of calcs when all items are not needed to be filled in by a vendor.

Tim

Reply | Quote

Hi Tim,

I’ve been working overtime on some additional projects, so I’ve had to wait on working on this. But your two suggestions are much more usable for me. It would be easy on those to collapse the areas that are not needed. I just need to find the time to sit down with this.

Thanks a lot for the ideas.

Vince

Reply | Quote