Ok, so I did the first of my workbooks. This workbook consists of 3 sheets and should be used to register a patient questionaire. I did it this way:

1st sheet (Input sheet): Each variable (patientID, date and one variable for each question) was created at the first row. Data validation ranges were applied to all variables to decrease the risk of errors. The first row was locked and the sheet was protected. I also took the time to hide unneccessary rows and columns. I also thought that freezing the first row and column would be a good idea.

2nd sheet: Patient ID and date variable refers back to sheet 1. Then, each question in sheet 1 had to be recoded to another number (0-100) using a convertion table that comes with the test. New variables were created on row 1, and I had to use a lot of IF-functions to make this work, like this:

=IF('SF36 input'!Z2=0;"";IF('SF36 input'!Z2=1;0;IF('SF36 input'!Z2=2;20;IF('SF36 input'!Z2=3;40;IF('SF36 input'!Z2=4;60;IF('SF36 input'!Z2=5;80;IF('SF36 input'!Z2=6;100)))))))

I tried to use the "format as table" tool in Excel 2007, but that didn't work out. It seemed like it didn't want to update sheet 2 when input was made in sheet 1. However, a working spreadsheet without the fancy styles is ok too. This sheet was then completely locked and protected.

Sheet 3 is where all recoded questions are put into groups (9 in all), and a mean is calculated for each group. So each patient ends up with 9 different scores (0-100). Patient ID and date refers back to sheet 1, and the new variables are calculated referring back to sheet 2:

=AVERAGE('SF36 rekodet'!Z3:AB3;'SF36 rekodet'!AD3;'SF36 rekodet'!AF3)

My only concern is that the workbook size is quite big (apprx 6MB), and I haven't started putting in any data yet :-). It probably has got to do something with me copying the formulas to a total of 300 rows (the expected number of patients will be somewere between 200-300). If there was a way of automatically copying all formulas to another row when I start input on a new patient, that would be very nice.

But appart from the size, should this workbook be ok to import into a master file later on?