It’s a bit labour intensive if you do it manually but here is a suggestion of
how you might go about it.
Create links from all the radio buttons to another sheet in the workbook so
that all the links are in Column A. The first group of 10 buttons to have
links in cells A1 to A10, the second group in cells A11 to A20, the third in
A21 to A30 etc. If you have 40 questions and 10 buttons each then you will
have some 400 to do.
How you go about setting the links depends on whether you used radio buttons
from the Control Box Toolbar or the Radio buttons from the Forms toolbar. If
you used the Control Toolbar then you have to click on the Design Mode icon
and then right click the radio button, select properties and then manually
enter the link address against LinkedCell. Example of the syntax is Sheet2!A1
..(You do not have to close this properties dialog box each time, simply click
on the next button). The linked cells will show TRUE for selected and FALSE
for not selected.
If you used the buttons from the Forms Toolbar then right click on the
button and select format control and then select the linked cell. The linked
cells for this is 1 for selected and 0 for not selected.
When you get the workbooks back, create a new folder to save them and save
them as something like Results01, Results02, Results03 etc. Use the zero
because it will keep them properly indexed.
Create a new workbook in the same folder and name it Summary or something.
Select Tools, Options, General tab and check the R1C1 reference style so that
your columns will then be numeric instead of alpha.
Open Results01 and copy the column of links and paste it into column 1 of
the Summary. Close Results01 and open Results02 and copy the column and paste
it into column 2 of summary, Results 03 to column 3 etc until you have copied
all. You will see why I suggested the R1C1 reference style because the column
numbers should match the Results workbook numbers and you shouldn’t loose
your place.
When finished copying the data, select Tools, Options, General tab and
uncheck the R1C1 reference style because it will make it easier for you to
understand the formula I am going to use.
Go to the far right of the data. (say column 61 if you have 60 columns of
data). If you used the Control Toolbox buttons and the results are TRUE and
FALSE then in the cell of the first row insert the following formula:-
=COUNTIF(A1:BH1,TRUE)
You will have to adjust the BH1 to match the last cell of data in the row.
If you used the Forms Toolbar and the results are 1 and 0 then simply sum
the row.
Copy this formula to the bottom of your data. You will now have the totals
for each radio button which was selected. You should be able to handle them
from here.
I did say it is labour intensive but writing these instructions have been
also. However, I suppose if it gets you over the problem then that is what
counts.
Just as a side issue. On the initial sheet you create, if you have TRUE
/FALSE from the Control Toolbox buttons then copy a FALSE cell and paste it
over all the linked cells and you will start off with all the buttons blank.
Same if you have 1’s and 0’s for forms toolbar buttons, Paste 0’s over all
the links.
Regards,
OssieMac
