- 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?
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 12:11 pm to LSUtigerME
quote:
The problem would be when they saved the file after picking, the next guy would see the picks last guy made.
Is there a way to have the macro read the players work log-in?
Posted on 9/17/14 at 12:51 pm to finchmeister08
I haven't done it before, but it's certainly possible.
Just tried a few variations I found online, the simplest being just
X=Environ("UserName")
I have no idea about the robustness or any other details. But it returned my username. I did not have permission to make a "declaration" statement to try some of the others.
Just tried a few variations I found online, the simplest being just
X=Environ("UserName")
I have no idea about the robustness or any other details. But it returned my username. I did not have permission to make a "declaration" statement to try some of the others.
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.
Posted on 9/17/14 at 1:18 pm to CubsFanBudMan
Another option would be to create a Google Docs Form. It would provide a spreadsheet with all of the submissions.
Posted on 9/17/14 at 2:36 pm to CubsFanBudMan
how does that help me with what i want? i don't mean to sound rude, i'm just trying to see what you're getting at.
Posted on 9/17/14 at 4:46 pm to finchmeister08
quote:
how does that help me with what i want? i don't mean to sound rude, i'm just trying to see what you're getting at.
I'm not sure which one of my options you are questioning. I'm a little unsure what your end goal is. I was assuming you wanted 1 page with everyone's picks on them, and that you didn't want to have to enter them on that page.
The way I do our office March Madness, I create the initial bracket, and email that out to everyone. As I get them back, I copy their picks to a new sheet in my main file. I have a sheet that I enter the scores for each game, and then the rankings get updated automatically. March Madness is a lot more complicated than a regular football pickem. I wouldn't be hard for me to set something up if I new what all of the parameters were.
Posted on 9/17/14 at 4:50 pm to CubsFanBudMan
sorry, i was talking about the google docs method.
eta:
i want everybody to have access to the spreadsheet via a server here at work. however, i don't want them seeing other peoples picks until after they inputted theirs. the reason i want this is because copying/pasting is a pain.
i wanted to use the access method cause it seemed simple. but i haven't used access since college and it was like reading greek.
eta:
quote:
I'm a little unsure what your end goal is
i want everybody to have access to the spreadsheet via a server here at work. however, i don't want them seeing other peoples picks until after they inputted theirs. the reason i want this is because copying/pasting is a pain.
i wanted to use the access method cause it seemed simple. but i haven't used access since college and it was like reading greek.
This post was edited on 9/17/14 at 4:55 pm
Posted on 9/17/14 at 4:56 pm to finchmeister08
quote:
sorry, i was talking about the google docs method.
The Google Docs method would allow you to create a form each week where each person would enter their name, and their picks. When the user submits the form, Google puts the data from that form into a spreadsheet form. You could then share that spreadsheet with everyone with read only access.
I'm not sure how you're doing the scoring (manually or with formulas in excel), but then you could download the spreadsheet as an excel file to do what you needed for scoring.
Posted on 9/17/14 at 6:26 pm to CubsFanBudMan
quote:
View and manage form responses
View responses
Once you’ve created your form and sent it to recipients, you’re able to view the responses you’ve received in three different ways: as a summary of responses, in a separate spreadsheet, or as a downloaded CSV. If you’d like a broad overview of how your group of respondents answered each question, viewing the summary is the way to go. If you’d prefer a fine-grained perspective on all the data you’ve collected with your form, you’ll likely want to view the responses in a spreadsheet or download a CSV with response data.
View the summary of responses
To quickly see how many users filled out a form and what their responses are, you can check out the response summary. From your form, click the Responses menu and select Summary of responses.
If you'd like respondents to be able to see this same summary of responses, check the box in the "Confirmation page" section of your form labeled Publish and show a link to the results of this form. When this box is checked, users who respond to your form will see a “See previous results” link, which they can click to view the response summary.
View form responses in a spreadsheet
To see a spreadsheet with responses to your form, click the View responses button in the toolbar. Learn more about storing responses in a spreadsheet.
Download responses as a CSV
To download all the responses your form has received, click the File menu, select Download as, and click Comma Separated Values.
Manage responses
Monitor for multiple submissions
As you're reviewing responses to your form, keep in mind that you can't prevent users from submitting a form more than once, so the same person may have submitted multiple responses. If you use Google Apps, you can choose to record the email addresses of people who fill out your form and then easily identify any duplicate responses.
Stop collecting form responses
To close your form to new responses, click the Accepting Responses button in the toolbar to toggle responses off. The toolbar button will then say "Not accepting responses." If you’d like to re-open your form to responses again later, you can click this button again to toggle responses back on.
If a form is no longer accepting responses, visitors to the form will see a message letting them know their responses won’t be collected. To customize this message, change the text that appears under the heading "This form has been turned off," which appears near the top of your form.
Copy a form or spreadsheet
You can copy any form or spreadsheet by clicking the File menu and selecting Make a copy. Copying a form will copy only the questions and layout, not the responses you’ve already received.
LINK
Posted on 9/19/14 at 4:22 pm to LSUtigerME
quote:
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.
after thinking it over, i think i like this idea better. Now, i know it's probably too late for this year, so i figured i'd get a jump on it for next season.
I hate to ask, but can you give me some tips on how to get started? i haven't used access in years.
Posted on 9/19/14 at 5:06 pm to finchmeister08
Download a template that comes close then customize.
Posted on 9/19/14 at 6:10 pm to finchmeister08
Honestly, I'm not a big fan of Access unless you have an extremely large amount of data/tables you're trying to link. For small data collection, I'm more in favor of using the forms within excel. They can function very similar to some Access functions.
The template in Access will probably the best place to start rather than going from a blank slate.
How complex do you want it to be? What did you have in mind?
I think this could be done simply and neatly in Excel (plus it's what I know much better). If I knew what you had in mind for the overall look/functionality, I could point you in the right direction.
The template in Access will probably the best place to start rather than going from a blank slate.
How complex do you want it to be? What did you have in mind?
I think this could be done simply and neatly in Excel (plus it's what I know much better). If I knew what you had in mind for the overall look/functionality, I could point you in the right direction.
Posted on 9/20/14 at 12:20 pm to LSUtigerME
Can I send you the spreadsheet i have so you can see how i have it set up?
Popular
Back to top
Follow TigerDroppings for LSU Football News