I’m designing a spreadsheet for our school district that will track assessments of student literacy. I have a few questions about putting it together.
We used to track the difficulty of books students could read using a scale that went from 1 (a beginning reader) to 40 (beyond fifth grade reading). We’re moving to a scale that tracks the same range but goes from A to Z. I’d like to be able to generate some graphs and averages of student progress, but I don’t think I can do that with letters–so I’d like to insert a function that converts from the letter to the number. That is, if a teacher enters in cell M28 that Bobby is reading at a level J, I’d like for N28 automatically to display the number 18, since J in the letter system is equivalent to 18 in the number system. Does that make sense?
Teachers enter data three times throughout the year (beginning, midyear, and end), on three different pages. The fourth page of the spreadsheet is a summary page. Teachers need to be able to filter, sort, and otherwise manipulate the results on the summary page in order to do some basic data mining. However, I’d really like it if they couldn’t actually change any of the values on the page. When I protect the worksheet, it seems to prevent filters and sorts as well. Is there a way to allow filters and sorts but to protect the content of individual cells? (The problem is the teachers who don’t pay attention during training and try to enter all their data on the summary page, which messes up other uses of the spreadsheet).
The worksheets sometimes have up to 40 columns. If columns A:E contain identifying information (say, school year, teacher name, grade level, and student last and first names), and rows 1:8 contain basic information (assessment type, etc.), how can I set up the spreadsheet such that when it’s printed, by default it comes out readable? I’d like for columns A:E and rows 1:8 to appear on every printed page.
if cell A1 contains the letter ‘B’, you can put in cell B1 the following: =CODE(A1)-64 This will make cell B1 display the number 2
Not sure here
You can set up which rows and columns to print on all sheets in the page setup area, look under the 4th tab, called sheet. There’s a box to repeat rows at top and at bottom.
Oh, this advice is for Excel 2003, we don’t use 2007 here, so hopefully 2007 is similar.
If you set up 4 columns above and wanted to return a numbered result for column “B” for a letter in column “A” then the formulat in cell B2 would be =VLOOKUP(A2,$C$1:$D$27,2,0)
It is important that you use a zero in the last part of the formula to return an exact number. If you use a one it will return the closest number.
Note: every number increment in the above example is 1.5 except for the last letter. The actual incremental number would be 1.538461538.
You can put your table on another sheet and when you reference it in the formula simply go to the page and highlight the area of the table when you get to that part of the formula. Use the dollar sign to lock in the cell references as you see them in the above example. The formula would look like this if you put the same table in the same spot in sheet2 =VLOOKUP(A2,Sheet2!$C$1:$D$27,2,0)
I don’t have any experience with what you are asking, but when I start up Excel 2003 and select Protect Sheet… under Tools|Protection, I see a whole bunch of checkboxes under “Allow all users of this worksheet to…”, including ones for “Sort” and “Use Autofilter”. Will those do what you want?
Probably so–I’ll play with that (and shoulda thought of that myself, given that I already knew that list came up–I’ve just gotten so used to clicking “ok” immediately on that screen that I forgot it even existed). Thanks!
Magellan, thanks for the explanation…this afternoon I actually got it to work, after a bit of finagling. (I thought I’d edited my second post to the thread to indicate that, but apparently not–oops!).
Dakota, I’m not sure what effect your solution would give, but i don’t think it’s what I need. The correspondence between letters and numbers doesn’t follow an even scale: they’re 1-1 near the bottom of the scale, then the numbers start skipping the odds, while the letters progress smoothly. I need to kluge together the old and new bureaucracies, which means the scale must be based on a two-column list. Thanks for the idea, though! The printing idea will work beautifully.
Then you must have “b” instead of “B”. Subtract 64 for caps, 96 for lower case.
Another solution to this one, using Magiver’s template, but even simpler than VLOOKUP, in B2 the formula could be =MATCH(A2,$C$2:$C$27,0), and you don’t need column D at all. But I like the =CODE solution - pretty slick. I’m not seeing the “scale” problem you are referencing.
I’ve already put something nearly identical to Magiver’s template into effect. I’m not sure how your solution would work at all.
There are two main differences between what Magiver suggests and what I did:
his columns C and D actually appear elsewhere, on a different worksheet entirely (where teachers with more curiosity than Excel knowledge won’t be likely to find them and fiddle with them); and
The actual columns C and D look something like this (I don’t have my exact notes here at home):
Given that I need to enter a letter in A1 and return the corresponding number in B1, what, besides VLOOKUP, could I use in B1 that would return 38 if I enter w, 18 if I enter k, and 1 if I enter a?
FWIW, the exact situation is that I need a value in n9 to return a result based on what’s in m9. Here’s the equation that works for n9:
Your formula as typed wouldn’t work if it was a remote sheet. it would look more like this =VLOOKUP(m9,‘[remote sheet.xls]sheet1’!$ba$10:$bb$35,2,0)
If you haven’t discovered it yet, you can’t have the lookup table in a separate sheet unless it’s open.
However you can hide the sheet so people won’t see it to mess with by using Format/Sheet/hide. You can also prevent it from being unhiddenwithout seriously tinkering with it.
Before you do any of this you can protect just the sheet with the table in it to prevent people from messing with it if they were to find it.
Finally, If you want to get clever, put the password in a comment and then go into Tools/options/view and click NONE under comments.
FYI, don’t try and type a data table location in a formula. Let Excel do it for you by going to the sheet you want to reference and then highlighting the area of the table. While the formula is still active, click on it in the formula bar and continue typing the rest of the formula.
Hmm…I may be using wrong terminology. The formula I posted was posted via copy-and-paste, and it works fine. The sheet “summary” is a different worksheet in the same spreadsheet.
The idea about hiding the worksheet sounds great. That way I don’t have to put it on the summary sheet, which users do need to be able to look at.
Good deal. A hint about hiding stuff. Don’t hide sheet2 and leave sheet3 active or people will go looking for it when they see sheet1/Sheet3. Rename everything or delete what you are not using.
Fair point. All the sheets have user-friendly names, like “Fall,” Winter," “Spring,” “Summary,” and “Individual Student Report.” I’ll probably name the worksheet with the conversion data something like “Back-end data (hidden)”
If you have a would-be Excel enthusiast who mangles your sheets then name the hidden sheet a remote cell number such as $aa$17, It will look like part of the formula.
Then put a conditional format in the actual cell that makes anything greater than .001 turn both the font red and the background red in that cell. Then link every cell around it to that cell and format them the same.
Anyone who goes to the remote cell and enters anything at all will turn that area of the sheet red and it will scare the hell out of them. Then you reference one of those cells to your active sheet with the words “Warning - calculation error” hidden with white text and a conditional format to turn the background red and the font black if the remote phony cell is touched. People will leave your stuff alone after that. I use to use this trick to highlight any sheet that I turned in to Accounting as a self check if columns didn’t equal out.
One of the things I did to fix my mangled sheets was to hide my formulas on another sheet and then make a macro that copied and pasted them over. That way someone could screw up a reference formula and all I had to do was hit the control key that launch the macro. Everything is made whole again.