Excel; correct architecture help needed

[Brigadier]

Morning all


I need a solution.

I feel that a database would be the correct solution, but writing one is above my paygrade, and I'm not likely to be able to get the funds to outsource it.

So that leave Excel, which I have a level of competence in.


From a bit of Googling, I think I've gone down the wrong route to start with.

1. I have created a workbook
2. it has three worksheets
3. worksheet ONE contains the raw data (the "answers). This needs to be absolutely beyond the retrieval of the candidate
4. worksheet TWO displays the RESULTS (of the answers submitted). This needs to be absolutely beyond the retrieval of the candidate (I don't want them inputting an answer, seeing the result, then iterating their answer until they get a result they're happy with.......
5. worksheet THREE is the worksheet into which the candidate inputs their answers. This needs to be the ONLY thing they see, and the ONLY thing they even know exists (from my point of view)

I've currently got it that


6. answer is input, into THREE
7. info. (in THREE) is processed in ONE, to give RESULTS
8. RESULTS are displayed in TWO


To my simple brain, I'd protect and hide ONE and TWO, leaving only THREE for the candidate.

After some Googling, this is supposedly not the correct way to do it (a diligent user can supposedly access ONE and TWO, which would render my crib sheet open for distribution).



How do I make this work (properly)?

a. with my current architecture, but make ONE and TWO totally secure from everyone but me (and a How-to would be appreciated, on how to do this), or
b. have a totally separate THREE, which I can then "plug in" to the (equivalent of ONE and TWO), thus keeping ONE and TWO physically away from any candidate? (Again, a how-to would be appreciated)



Thanks in anticipation

 

[TONY1966]

Wouldnt it be easier for the candidate to just have a spreadsheet with submitted answers in it then have a master file (accessible only ro yourself) that uses vlookup against that candidate spreadsheet?

 

[Brigadier]

Wouldnt it be easier for the candidate to just have a spreadsheet with submitted answers in it then have a master file (accessible only ro yourself) that uses vlookup against that candidate spreadsheet?

Thanks Tony, that's why I'm asking!


I really don't know what is the best / standard way of dealing with this.


Like I said, some things I can do, but I'm very baby-steps with Excel (use it to assist my job, not as an integral part of it).

 

[Brigadier]

How would your solution work then?


Here's my guess; please correct it as you need to.


1. candidate submits their completed .xlsx file
2. I copy it to Folder A (where my master spreadsheet lives)
3. My master spreadsheet has VLooKUP command(s) in relevant places, to the candidate's .xlsx (using filepath involving Folder A, I presume)
4. Answers get produced in my master spreadsheet


Or, have I got the completely wrong end of the stick?

 

[mcprinter]

Is it multiple choice answers?
For a Vlookup to work, the string being looked up needs to match the answer exactly.

Xlookup is a newer, improved, easier to use version of Vlookup.

 

[IT Minion]

I'd you've got Office 365 available you could do your data collection using a List and then pull that data into Excel to transform it and process it.

Or just process it all using a flow.

Is this manipulating numbers or is it more of a multiple choice quiz/test?

 

[robinbanks]

Tell us the actual problem you're trying to solve, rather than the issues you've hit with the approach you've been able to develop with your present understanding?

(This is XY, if you're wondering/want to google. We try not to XY when using computers to solve problems)

 

[Brigadier]

Candidate does a proficiency test.

I have an "exact" result, which they have to be within a certain range of.

They do their work, then submit their answer.

I then take their answer, input it into my master spreadsheet, and it spits out:

- their calculated result (based on their answer)
- how this calculated result compares to my "exact" answer
- whether this calculated result falls inside or outside of the acceptable range


As the candidate submits many answers, and there are many candidates, and I receive lots of submissions per day, it is a ballache from purely data input point-of-view: I'd rather the candidate (effectively) does the data input themselves, but without them having any access to the master spreadsheet (so that there is no possibility of them finding out the "exact" answers dataset).




I did think that I could have my master sheet have a proxy candidate sheet, which "looks up" the candidate's submissions, then does it like that.

Then, providing that the candidate doesn't alter the filename, and I put their sheet in the same network location, the "look up" filepath formula will do the rest, correctly.



Thanks all, for help so far.

 

[IT Minion]

Standard scored quiz then.

What tools do you have available? Office 365, Google Docs, or just a desktop with excel?

Do the users have their own windows account or are they using a shared account and just putting their name in the excel?

Are the answers multiple choice or are there any numbers involved?

 

[Brigadier]

What tools do you have available? Office 365, Google Docs, or just a desktop with excel?

365, and Excel.

Do the users have their own windows account or are they using a shared account and just putting their name in the excel?

I do not understand your question.

Are the answers multiple choice or are there any numbers involved?

As previously stated, they produce a result - say, 5.5 - and I check whether it falls inside an acceptable range.
They then get a pass, or fail.
Rinse and repeat, for as many as they submit (could be as many as 20 at a time).

 

[IT Minion]

A 365 Survey will let you get the responses from people without them being able to see each others answers.

You then export the results to excel and score those against your mark sheet to generate a score.

You could do the same with a SharePoint list but it is more work. On the other hand you could automate the scoring with a Flow and send out results by email if you wanted. With a SharePoint list you can connect it to an Excel spreadsheet so you just refresh the data connection. You might be able to do the same with a survey but I haven't tried it.

 

[robinbanks]

As previously stated, they produce a result - say, 5.5

Are the answers always a number that lies within a range?

 

[Brigadier]

Are the answers always a number that lies within a range?

The answers are always a number, yes.
From 0.0 to 2000.0

Always stated to 1 decimal place.


The candidate submits their effort(s) - again, as a number, to 1 d.p. - which have to be within a certain range of my correct answer for each.
Otherwise, they fail that particular question.

 

[robinbanks]

Hmmm. Sounds like an interesting challenge to set as a competency test for an interview. I'll have a play and see how long it takes..

 

[robinbanks]

PM'd you