Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I came across an Excel form that had some interesting functionality behind it: Expert Review Checkpoints

What is the basic approach to how this is accomplished in Excel? Some discrete features in it that I wasn't sure how to accomplish are:

  1. "Locking" all but some cells so that they could not be edited
  2. Restricting input to specified acceptable values
  3. Determining when a tab has been completed and making a calculation (on the "results" tab) only after it has been completed
  4. Determining how many cells in a given column have been filled in
  5. Making a calculation across tabs
  6. Generating a multi-axis chart on the "results" tab from the calculations
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

That's an impressive spreadsheet. There's not too much to it beyond what you've observed:

  1. Any cell or group of cells can be locked by right clicking and choosing Format Cells and the Protection tab, clicking locked, then choosing Protect Sheet under the Review tab.

  2. See Data Validation under the Data tab, you can set a list of numbers for a given cell or use a range of values that already exists.

  3. If you put in a value in one of the tables, it does not wait to tabulate the results, they are updated when you change the cell (via a recalc of sorts).

  4. See cell F14 on the Results page, it's a COUNT() worksheet function

  5. See that same cell for an example, the other sheet is referenced by 'Sheetname'!Range

  6. Charts can also access data from any other sheet via the same method in #5

share|improve this answer
    
These are the locations of those behaviors in Excel 2007, they may be very different in 2003 (as that sheet was made in compatibility mode, I'm not sure of its version of origin). –  jonsca Dec 13 '11 at 21:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.