Excel 2010: Can a table be password protected based on the value in a filter?

Hello,

This is my first time posting to the forum so, I apologize now if I commit any offense in doing so.

I have a large table (3,000 rows, 200+ columns) where I store in-depth information (columns C - IB) by Supplier (column C), product name(column G) & and package size (column H).

I would like to be able to share this spreadsheet with multiple suppliers but only grant them access to see the information specific to them. Ideally it would be as easy as the supplier being able to click on the "Supplier" filter, click on their supplier name and have a box pop up asking for a password and once said password was entered, they could then view/change/filter information. Of course, each supplier would need a different password.

Another thought I had would be to have the spreadsheet open to a separate sheet, where there would be a data validation box that the supplier could click on and then plug in their password - in the background excel would filter the table to that supplier and protect everything else so as to not be visible...

I've searched and searched the interwebs but haven't been able to find anything similar. Any help would be greatly appreciated.

Cheers!
Lu

I will be looking forward to reading any responses you receive.

Note that it's not all that hard to crack an Excel password. If the data is sensitive, any solutions you get you may not wish to use, for that reason.

An alternate idea, that I'm sure you've already considered, is to have just one master file that you maintain. You would then also create a macro that would generate a new spreadsheet specifically for each supplier, using the current data in your master file.

This way each worksheet that you provide to each supplier ONLY has the data specific to them.

Well, no, I hadn't even given that a consideration - I have truly overthought this, your way would be much quicker. That was my reasoning behind posting to the forum to begin with, I was tired of having to manually create a copy of the workbook and delete the information I didn't want being shared. A macro would do the trick nicely and I am sure I can find half a hundred tutorials online to help with that. Thank you for your reply! :o)