Formulaic Solution Required to differentiate between numeric and formulaic entry!

[Phil_r_c]

I am trying to write a self-marking assessment for classes of young school pupils I teach and I have struggled to find a solution to a key issue:

How can I automatically detect if a student has used a formula or worked out the correct values to posed problems rather than solved it in their head (or on a calculator!) and simply typed the numeric value in?

With over 154 scripts to mark each time the test runs a foolproof way to show that pupils are using Excel effectively is essential; as it shows they are working at a higher academic level which in turn has to be reported back to senior managers and parents.

Due to security restrictions on our school network any workbooks used can't have macros embedded into them as the macro functionality is blocked to all but a very privileged few!</SPAN>

Any help/guidance gratefully appreciated! </SPAN>

 

[T. Valko]

To make the GET.CELL function able to use relative referencing...

Create this defined named formula:

Name: IsFormula
Refers to:

=GET.CELL(48,TEXTREF(SUBSTITUTE(MID(GET.FORMULA(TEXTREF("rc")),17,100),")","")))

Then, use this formula on the worksheet:

=IF(1,IsFormula,cell_reference)

Where cell_reference is the cell reference of the cell you want to test for the presence of a formula. For example, to test cell D10:

=IF(1,IsFormula,D10)

This formula can be entered in any cell (except the cell it references of course!).

Since this can not be done without some form of macro functions or VBA, why not just use a VBA UDF? It'd be more efficient and much less convoluted.

 

[Phil_r_c]

As I found out at about 1am, the saved file format will not be compatable with our network as the policy of "macro's will not be enabled for students, it poses too great a security risk." means the saved file type will just not run on the pupils machines.

Though leads me to the question, in a network that is macro disabled, would the rest of the spreadsheet still function as normal, save when requested and then function as I need when used on a "less secure" machine such as a personal machine at home?

Thanks to all, this has been quite a learning curve, using facilities teachers do not need to use on a regular basis.

 

[Snakehips]

As I found out at about 1am, the saved file format will not be compatable with our network as the policy of "macro's will not be enabled for students, it poses too great a security risk." means the saved file type will just not run on the pupils machines.

Though leads me to the question, in a network that is macro disabled, would the rest of the spreadsheet still function as normal, save when requested and then function as I need when used on a "less secure" machine such as a personal machine at home?

Thanks to all, this has been quite a learning curve, using facilities teachers do not need to use on a regular basis.

Phil,

Can you confirm. Does the network allow you open a macro enabled file but with the macros disabled or does it totally reject files with .xlsm extension?