Excel tips: How to populate cells after locking other cells

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.

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.

Another option would be to create a Google Docs Form. It would provide a spreadsheet with all of the submissions.

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.

sorry, i was talking about the google docs method.

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.

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

Download a template that comes close then customize.

Can I send you the spreadsheet i have so you can see how i have it set up?

Sent.