1

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

1 Answer 1

3

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

1
  • 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, 2011 at 21:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .