A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Hide and restrict rows for each user to view his own record only



 
 
Thread Tools Display Modes
  #1  
Old August 23rd, 2004, 09:07 AM
Pleo
external usenet poster
 
Posts: n/a
Default 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  
Old August 23rd, 2004, 08:02 PM
hgrove
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 11:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.