|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
This requires Office 2021 or Office 365, because it uses the FILTER function
Say you have an Excel file with lots of data that must be frequently searched by various people, but you don't want them to see all the data - only parts of it. At the same time you want to be able to see everything and update the data from time to time, and these updates to be reflected in what others see.
Assuming your entire data is in a sheet called "Raw data", create another sheet called "SEARCH".
Select the entire data range in sheet "Raw data" (headers included) and press CTRL+T to get a table - let's name it TBL1.
This way, when you update your data by inserting new data at the end of the existing one, it is automatically included in the table and it will be automatically reflected in the search.
For the search to work properly, you would have to arrange the columns you want people to see in an adjacent (consecutive) manner, in the desired order.
If you don't want to arrange the data in a way that the columns people will be allowed to see are adjacent, then you should create the set of columns at the end of TBL1, filled with formulas that equal the desired values from the raw columns. This might make your excel slower though.
For the sake of the example, let's say the columns you want people to see/search are the first 5 columns in your raw data: Name, Department, Sub-department, Department chief, Instructions.
In the SEARCH sheet, leave 3 rows empty, then on row 4 name the columns you want them to see - can be different names from columns in the raw data. I'm just naming them the same as above. The order must remain the same.
In cell A1 you can type something like "Please type a partial word to search, then press enter:".
Make the 1st column wider, and the 2nd row wider, to make a search box in cell A2. I would color it differently (yellow?).
In cell A3 type the following formula: ="*" & SUBSTITUTE(A2;" ";"*") & "*"
This way cell A3 will ensure that whatever is typed in cell A2 will be accompanied by wildcards at the beginning, end, and instead of any space character. So if the person types a couple or more words, table will be filtered with wildcards at the beginning, between the words, and at the end.
Now, assuming the column headers in the SEARCH sheet are on row 4 in A4:E4, in A5 type:
=FILTER( TBL1[[Name]:[Instructions]]; ISNUMBER( SEARCH( $A3$; TBL1[Name] & TBL1[Department] & TBL1[Sub-department] & TBL1[Department cheif] & TBL1[Instructions] ));"")
Once you press enter for the formula above, it will automatically populate a range in the SEARCH sheet, corresponding to the amount of data in those columns in the "Raw data" sheet. Now if you type something in cell A2, the list should filter to display the results based on the word/s in A2 with wildcards (since the FILTER formula above refers in fact to cell A3, which has the wildcard formula based on cell A2).
Now you can hide the raw data sheet and protect the workbook with a strong password.
To enhance this even more, you can do the following things, before locking the workbook:
- add a formula in Conditional formatting for the range of the search results (FILTER area), that says for A2 empty (=$A$2="") to color FILTER area font in white, A2 not empty (=$A$2<>"") color the area font in black - this way when there is no searched term, no list is shown (text is white, so the list is actually hidden) - and when there is a searched term, results are shown in black text - become visible
---- the downside to this is that if they type a space character in A2, they will see the entire list (as space is replaced by wildcard and searching for just a wildcard finds all things)
---- to counter that downside, you can make an additional condition in Conditional formatting: =LEN(TRIM($A$2))=0 and color the area font in white. Make sure you put this condition below the one with =$A$2<>"" and font black, otherwise any search would return white text
- unlock cell A2 (right click on it - Format Cells - Protection - uncheck "Locked"), and when you protect the sheet make sure do not allow "Select locked cells" (uncheck it). Also, when protecting the sheet do not allow more stuff that might lead to the user changing the sheet's structure (like insert rows etc.). You must leave the "Select unlocked cells" option checked, so they can select cell A2 for searching
---- this way they will not see the formulas and will not be able to select anything else besides cell A2
---- a benefit of this is that when they press enter in cell A2 - to search - the cell will remain selected so they can type the next word to search directly without having to first put the selection in A2 again (since it's the only cell that is unlocked and you cannot select locked cells)
- to lock the sheet while allowing the users to filter the results (in case you add a filter to row 4), when protecting a worksheet make sure you leave unchecked the "Sort" while checking the "Use AutoFilter"
- now lock the SEARCH sheet with a strong password, then lock the workbook with a strong password
- test before anyone else
|
|
