You have created a beautiful bid sheet template and locked cells or defined input format with data validation - only to find that the structure has been corrupted by copy-pasting or changing input settings. The file becomes unusable and has to be manually corrected, before you are able to analyze any data...
To avoid a situation like this, follow the steps in the 1️⃣ first approach below to lock your sheets completely.
If you trust hat your suppliers will not try try to upload or share a modified file, scroll down to follow the steps explained in the 2️⃣ second approach.
1️⃣ How to lock Excel sheet so only certain cells (Supplier cells) may be selected and altered
Press CTRL + A to select all cells in a tab and right click and select Format Cells
Choose the tab Protection and make sure that the tickbox Locked is activated. It should be displayed as a tick symbol ✅
Now select all the cells the supplier should be able to fill out. Right click and choose Format Cells again. In the tab Protection, you should untick Locked now.
In the Excel menu bar, navigate to Review and click on Protect Sheet.
Ensure that the tickbox Select locked cells is not activated and set and confirm a password.
We recommend to activate the following checkboxes:
☑️ Select unlocked cells,
☑️ Sort, and
☑️ Use Auto Filter
to allow your suppliers to navigate the bid sheet easier.
Click on OK to enforce the protection.
Your worksheet is now protected and cannot be copied!
💡 Tip: Make sure you note down the password OR - when working closely with your Customer Success Manager or in case of a longer absence/holidays - share it with your contact at Archlet to ensure that any changes or adjustments when creating a parallel Bid Importer project can be done.
2️⃣ How to lock Excel sheet so Suppliers can adapt the file but not change the content
What if you want to allow your suppliers to be able to copy-paste the table or to insert columns that would help them to add their own item identifiers or do calculations?
In this case, we would suggest the following settings:
☑️ Select locked cells
☑️ Select unlocked cells
☑️ Format cells
☑️ Sort
☑️ Use AutoFilter
❗ Please note:
Allowing suppliers to copy-paste the entire table and adjust it's format can lead to errors when uploading the modified file later on.
In an RFx project: Suppliers will see that entries have been ignored or that the file does not correspond to the available bid sheet if they try to upload a file. They need to transfer their bids and any other supplier inputs into the correct bid sheet template, save it and upload this file.
In a Bid Importer project: When uploading supplier files, you might experience insufficient/incorrect column matching or see errors when validating the bids (due to circumvented data validation). In this case, either copy-paste and correct the supplier input by using the bid sheet template or ask the supplier to share a corrected version themselves.