If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hide and restrict rows for each user to view his own record only
Source worksheet
[ID] [Name] [Date] [Attend Time] 123 MARY [16-Aug] 09:00 ~ 18:00 124 JOHN [16-Aug] 09:00 ~ 18:00 125 SUE [16-Aug] 09:00 ~ 18:00 123 MARY [17-Aug] 09:00 ~ 18:00 124 JOHN [17-Aug] 09:00 ~ 18:00 125 SUE [17-Aug] 09:00 ~ 18:00 123 MARY [18-Aug] 09:00 ~ 18:00 124 JOHN [18-Aug] 09:00 ~ 18:00 125 SUE [18-Aug] 09:00 ~ 18:00 ...................... ================================================== ========= A user receive this sheet and then he will input ID no. at a cell. [Input ID No.] (Remark: User Mary inputs 123 here) The sheet will display Mary's record only. All other record (rows) will be hide. [ID] [Name] [Date] [Attend Time] 123 MARY [16-Aug] 09:00 ~ 18:00 123 MARY [17-Aug] 09:00 ~ 18:00 123 MARY [18-Aug] 09:00 ~ 18:00 Now I use the filter method only but users can view all records. I want to restrict user to view his record only instead of create user own record sheet for each user. Can I do that? Please help. Thanks |
#2
|
|||
|
|||
Pleo wrote...
... A user receive this sheet and then he will input ID no. at a cell. ... The sheet will display Mary's record only. All other record (rows) will be hide. ... Now I use the filter method only but users can view all records. I want to restrict user to view his record only instead of create user own record sheet for each user. Can I do that? . . . Best approach would be to put the table in another worksheet that you would hide, then use formulas to pull records from the table in the hidden worksheet. However, clever users would still be able to see other users' records. There's no truly secure way to do what you want with any spreadsheet, so you must accept some insecurity. Using formulas to pull records from hidden worksheet ranges usually provides adequate security against unsophisticated users. If your table were named TBL with IDs in the first column, the user enters her/his ID in a cell named IDEntry, and your first/top extract record would be in A22 of another worksheet, you could pull all records matching that ID using array formulas like A22 [select these four cells then type formula] =VLOOKUP(IDEntry,TBL,{1,2,3,4},0) A33 =IF(COUNTIF(INDEX(TBL,0,1),IDEntry)COUNTA(A$2:A2) , INDEX(TBL,MATCH(COUNTA(A$2:A2)+1, COUNTIF(OFFSET(TBL,0,0,ROW(INDIRECT("1:" &ROWS(TBL))),1),IDEntry),0),0),"") Note: enter array formulas by holding down [Ctrl] and [Shift] keys before pressing [Enter] (Windows) or holdind down the [Cmd] key ebfore pressing the [Return] key (Mac OS). --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Hide and restrict rows for each user to view his own record only | Pleo | General Discussion | 6 | August 23rd, 2004 08:01 PM |
Hide and restrict rows for each user to view his own record only | Pleo | General Discussion | 1 | August 23rd, 2004 05:49 PM |
Hide and restrict rows for each user to view his own record only | Pleo | Worksheet Functions | 0 | August 23rd, 2004 08:52 AM |