- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel tips: How to populate cells after locking other cells
Posted on 9/17/14 at 10:31 am
Posted on 9/17/14 at 10:31 am
We're doing a college football pick 'em here at work. the set up we have now is i email the spreadsheet out with the picks and the spreads for each game and through out the week, they resend the spreadsheet with their picks. this can get time consuming.
My idea is to put the spreadsheet on a server to where all the players can access it and put their own picks into the spread sheet and save it. However, that can cause chaos due to them being able to see other peoples picks before their own. so, i was going to assign passwords to each column/player that i would be the owner of. once they enter their picks, they would proceed to lock their picks, and the other cells/picks from the other players would automatically show. and if they wanted to change their picks, they couldn't cause they wouldn't know the password to their column.
did any of this make sense?
My idea is to put the spreadsheet on a server to where all the players can access it and put their own picks into the spread sheet and save it. However, that can cause chaos due to them being able to see other peoples picks before their own. so, i was going to assign passwords to each column/player that i would be the owner of. once they enter their picks, they would proceed to lock their picks, and the other cells/picks from the other players would automatically show. and if they wanted to change their picks, they couldn't cause they wouldn't know the password to their column.
did any of this make sense?
2
Posted on 9/17/14 at 11:54 am to finchmeister08
Not exactly clear.
To answer the title question, it's pretty easy with a macro. If they are savvy and want to cheat, they can. You could just build a macro that they press to "lock-in" their picks. This would unlock the workbook structure, and hide/unhide worksheets, then lock everything back.
The problem would be when they saved the file after picking, the next guy would see the picks last guy made.
You could possibly also build it in an access database. This would have similar coding, but eliminate the opening/saving issue. Their picks would be recorded to a master list that only showed after selecting/locking all their picks.
Another way to address it is to fix the "time-consuming" part. Come up with a format that you send out, and have them return with a specific filename, ie "Pick Em - John Doe". Once all spreadsheets are returned and unmodified (locking cells and data validation), just have a simple macro that "builds" your master spreadsheet, labeling by filename.
To answer the title question, it's pretty easy with a macro. If they are savvy and want to cheat, they can. You could just build a macro that they press to "lock-in" their picks. This would unlock the workbook structure, and hide/unhide worksheets, then lock everything back.
The problem would be when they saved the file after picking, the next guy would see the picks last guy made.
You could possibly also build it in an access database. This would have similar coding, but eliminate the opening/saving issue. Their picks would be recorded to a master list that only showed after selecting/locking all their picks.
Another way to address it is to fix the "time-consuming" part. Come up with a format that you send out, and have them return with a specific filename, ie "Pick Em - John Doe". Once all spreadsheets are returned and unmodified (locking cells and data validation), just have a simple macro that "builds" your master spreadsheet, labeling by filename.
This post was edited on 9/17/14 at 11:58 am
Posted on 9/17/14 at 1:15 pm to finchmeister08
The simplest thing would be to have a summary tab that references everyone's picks. When each person sends you their picks, "move" them to the master file, and rename the tab "Entry #". Then you can use the indirect command to reference the sheet name and cell #. This is how I do our March Madness at work.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News