Data Table not quite what I need... (1 Viewer)

[TomH]

Hello. I need some help with a project I am developing for an accounting course I teach. In this project, the students are given 13 spreadsheets in a workbook, each of the first 9 of which having a number of cells in which they must make entries (about 50 in all). All students have common entries in about 30 of these cells, but in 20 they have unique entries so that no two people have the same entire project. The student performance is judged on four output cells that calculate from the inputs made by the student. All of their inputs are referred to by established calculations and then their final values in pro forma financial statements are graded.

So... when building my key, I can fill in the common values... but how do I set up inputs 1-20 and outputs 1-4 in a single spreadsheet? I've used a data table before, but that's not quite this I don't think. What I need here is some way of building a table of inputs so that when I have the name in column A, and all their inputs shown in columns B-U, the workbook would theorize the placement of those values in the various correct spots in the workbook and then report back the calculation the four outputs in columns W-Z. I have 105 students, so I would have 105 sets of data for this. The way I think of it is "if these 20 values were entered into these 20 cells, this would be the result for, for example, Net Income." Then, in the row below, the another set of 20 and a different answer.

I hope I have explained this well... and I appreciate any help!! I have attached a sample file that emulates what I need on a smaller scale. THANKS.

Tom​

 

[NickHa]

One way of doing what you want is in VBA. I have updated your spreadsheet to show how this is done. Look at the code behind 'Sheet1', which is reproduced here

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Rem determine whether a change in the 'load' cell brought us here
If Target.Address <> Range("Load").Address Then Exit Sub
Rem 'load' has changed - search NAMES to find a match, restricting search to the NAMES column
Set cl = Range("entriestable").Columns(1).Find _
                              (What:=Range("load"), _
                               LookIn:=xlValues, LookAt:=xlPart, _
                               SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                               MatchCase:=False, SearchFormat:=False)
Rem check if an entry was found
If cl Is Nothing Then Exit Sub
Rem plug calculated result into total column for current row
cl.Offset(0, 4) = Range("project") ' TOTAL is four columns to the right of NAMES
End Sub

(use <Alt><F11> in Excel to get to the code editor)

There are other ways of achieving the same result, but without knowledge of how you gather the results from your student spreadsheets, I can't be more specific. By way of example, if you had all of your students' workbooks in a single folder, which also contains your summary workbook, you could use cross-workbook cell references to read the results directly (but this assumes that all student workbooks are identically structured and have predictable names, amongst other things).

 

[TomH]

There are other ways of achieving the same result, but without knowledge of how you gather the results from your student spreadsheets, I can't be more specific. By way of example, if you had all of your students' workbooks in a single folder, which also contains your summary workbook, you could use cross-workbook cell references to read the results directly (but this assumes that all student workbooks are identically structured and have predictable names, amongst other things).

OK. Editing this entry since it went so quickly. I see that your code fills in the appropriate line with its answer, and then sets it as a value so that it does not empty when the LOAD entry is changed. So, all I would have to do is cycle through the 105 LOAD values to get all my answers. That's a big leap forward. Is there a way to automate the entry into the LOAD value each of the NAMES, perhaps through a macro? Great advancement!

 

[NickHa]

Yes, a macro can be written for your situation! The method of implementation should change from what you have at present, because it is not necessary to copy data between cells in order to carry out calculations. I would put the algorithm into code for each of the result categories so each output is calculated sequentially based on each student's results.

I have various thoughts about how to solve the problem, but I need to know more about the situation. Some questions:
1. Are all spreadsheets identical in terms of structure? In other words, the cell references of interest are not changed by your students - all they do is enter results into predefined cells?
2. Is there a standard naming convention for the student workbooks? Is some form of student identifier included in the name of the Excel file?
3. Is the student's full name incuded in the workbook anywhere (i.e. in a cell in one of the worksheets)?
4. What is the process by which you gather the student results? Do they e-mail their workbooks, put them in a shared folder on a public drive? What do you do with them to prepare for extracting the results (e.g. rename the files, move them somewhere etc.)?
5. Which version of Excel are you using? Are your students using the same version?

there might be an alternative method of collating the results, if the workbooks are always identical in structure and have predictable names - which is why I'm asking these questions.

Can you post an example of the student workbook as completed (making it anonymous, of course)?

 

[TomH]

1. Are all spreadsheets identical in terms of structure? In other words, the cell references of interest are not changed by your students - all they do is enter results into predefined cells?
2. Is there a standard naming convention for the student workbooks? Is some form of student identifier included in the name of the Excel file?
3. Is the student's full name incuded in the workbook anywhere (i.e. in a cell in one of the worksheets)?
4. What is the process by which you gather the student results? Do they e-mail their workbooks, put them in a shared folder on a public drive? What do you do with them to prepare for extracting the results (e.g. rename the files, move them somewhere etc.)?
5. Which version of Excel are you using? Are your students using the same version?

Nick: THANKS for your help. I am attaching a generic version of the actual file I am working on right now (still need to do some cleanup on the values input... but it works. The only difference between this workbook and the one the student gets is, of course, the answers and required calculations aren't there for them, and they don't get the two INPUTS tabs. Oh, and they don't get the values in gray on the production and inventory related pages... I use those projections to make the calculations simpler in the key.

If you look at the word doc, you'll see the mail merge template. That is filled by the data on the INPUTSfixed tab in the excel workbook. That information is also used in a named range called table referred to in a number of VLOOKUPS throughout the workbook. The students must fill in the shaded cells. The darker shaded cells are common to all students, the lighter ones are unique to each student. Then, the results are reported back on the INPUTSfixed tab and, as a matter of checking, the LOAD cell in A111 of that page can be entered, which changes all of the values throughout the sheet to solve for that particular student... allowing checking of not only the check digits they submit but also the spreadsheets they turn in in hardcopy (as a paper submission as if they were doing a real budget) if necessary.

What I really want to do is fill in the area to the right on the INPUTSstatic tab in each line of the table. One at a time is fine for checking, but I want to publish the check digits after their first submission so that they can correct and submit their changes... so I need them all, and I would rather not do 103 copy-pastes. If there's a way to get this done, it will be a huge timesaver for me going forward. THANKS!!

---

1. Yes. All they do is enter values or formulae into predefined cells.
2. No. Their submission is done in hardcopy. They download the blank workbook, fill it out, print and submit it (with numerous other documents as part of their project).
3. No. Not separate from all the other names, though they do all appear as a list on the INPUT tabs.
4. No electronic submission.
5. I'm assuming everyone has 2010. That's what I have (NOT happy - argh) though I built this in an earlier version (2003 I think). I just completed the VLOOKUPS to post this, and my assistant is still checking those to verify correctness.

Thanks again.

 

[NickHa]

4. No electronic submission.

If the students submit hard-copy worksheets only, then I assume there is a process of data-entry that you follow, to key-in the results? If I have understood this correctly, then what is the starting point for your copy-paste exercise - is it the INPUTSdynamic worksheet?

What I really want to do is fill in the area to the right on the INPUTSstatic tab in each line of the table. One at a time is fine for checking, but I want to publish the check digits after their first submission so that they can correct and submit their changes... so I need them all, and I would rather not do 103 copy-pastes. If there's a way to get this done, it will be a huge timesaver for me going forward.

I assume you mean INPUTSfixed tab? If so, you mean cells starting at D2?

What are the check digits to which you refer?

Yes, it is quite straight-forward to automate any repetitive process through code, once the steps are defined.

Can you describe the process you follow in more detail so I can undestand the step-by-step actions, please? Am I correct in inferring that this workbook (your attachment) is the only one involved?

Why does tab INPUTSdynamic have the RANDBETWEEN function in its cells - is this just for testing purposes?

I'm not sure of what you need, having checked back to your previous uploaded workbook. I'm taking it that you want to step through the INPUTSfixed table and carry out the calculation for each row (student) and then put the result back in one of the columns (which one?). That too is quite straight-forward,but before I start on this, I'd like to better understand the requirement!

 

[TomH]

If the students submit hard-copy worksheets only, then I assume there is a process of data-entry that you follow, to key-in the results? If I have understood this correctly, then what is the starting point for your copy-paste exercise - is it the INPUTSdynamic worksheet?

They use the merged information in the word document and have to build in the values to the worksheets. The values in the INPUTSfixed are used to merge into that doc. The INPUTSdynamic is there just so I have a way of building a random set of values each time I assign this. I'm still working on a macro that will copy those values over to INPUTSfixed and format the values correctly to use in the merge each class. That part I can do OK.

I assume you mean INPUTSfixed tab? If so, you mean cells starting at D2?

OOPS. Yes.

What are the check digits to which you refer?

The four values that they have to report at the bottom of their merge document are the values I need to create in the key. The way this is written now, using the vlookups in the INPUTSfixed sheet and the budget sheets, I can select a LOAD value, and all the values calculate throughout the workbook to that student's values. The four check values then can be compared to the student's submission. They are over to the right on the INPUTSfixed tab, columns Y-AB. Also, in row 111 as summarized for a single student.

Can you describe the process you follow in more detail so I can undestand the step-by-step actions, please? Am I correct in inferring that this workbook (your attachment) is the only one involved?

What I am trying to do is have that whole section of columns Y-AB filled so I can build a publishable document with all the check digits for the students to check their own work after submission.

Why does tab INPUTSdynamic have the RANDBETWEEN function in its cells - is this just for testing purposes?

I use that to restrict the values to a narrow range. For example, "Sales grows at a rate of X% per quarter". I want that to be, for example, restricted to be between 5-10%. I need to clean that up, as I started to do it one way on formatting and can simplify those a lot.

I'm not sure of what you need, having checked back to your previous uploaded workbook. I'm taking it that you want to step through the INPUTSfixed table and carry out the calculation for each row (student) and then put the result back in one of the columns (which one?). That too is quite straight-forward,but before I start on this, I'd like to better understand the requirement!

Well, there are four outputs that I am looking for for each student, in columns Y-AB. So, on the whole... there are VLOOKUPS throughout the workbook that grab data from the INPUTSfixed tab, named range "table". The resulting values are reported by an IF statement in columns Y-AB. I just want that whole section filled, whereas with my own poor VBA skills I cannot do. I tried writing a macro but I just hit a wall and don't know how to complete it.

In the end, all I want is a key in which I can randomize entries on the INPUTSdynamic page, copy that over as values into the INPUTSfixed page, have the check digits publish in the sheet for all students, and then be able to publish that data spearately to the students after submission.

The exercise has other arcane and draconian rules, such as no published due date. After they've had it for a week or so, I tell them in class that it is due by 5PM (or whatever time) or they get a zero... and they go into panic mode to get it done. The submission has to be formatted correctly and all pages in the right order, etc... or they get huge points penalties. Then, after the panic and they get it in on time, I stamp it "DRAFT", hand it back and give them 48 hours to correct it, having published the check digits from columns Y-AB.

What I am trying to accomplish here is to end having to copy and paste as values each line of 4 values individually (in this semester, 103 times). This will have no effect on the student... I am just trying to build a more user-friendly key.

Thanks again for your help. I appreciate all the time you've taken already!

Tom

 

[NickHa]

I have amended your workbook to include a macro behind sheet INPUTSfixed - no other changes made.

I used the double-click event for the worksheet to trigger the macro to run. If you open the workbook and double-click in any cell in INPUTSfixed, you will see the cells in range Y2:AB104 populate with the results. The populating process should be invisible, unless you have a slow PC.

The logic I used is to put the student number in the 'load' cell, then copy the computed values in the VLOOKUP formulae in range Y2:AB104 and replace them with the values (i.e. copy/paste special, values).
The loop has a counter which it starts at 1 (which is student 1, but relies on the first student being in row 2) and increments by one for each iteration. When an empty cell is encountered in column C, the macro stops.

I included comments in the code to explain what is happening - I hope these are clear!

As you are using Excel 2010, you could put a custom button in the ribbon to invoke the macro, but for now the double-click will work.

I think this does what you need. Any problems, let me know.

 

[TomH]

I have amended your workbook to include a macro behind sheet INPUTSfixed - no other changes made.

Nick:
Thanks very much. I read your code... that was very clever on the counting through the data column, and using OFFSET. Thanks again!
Tom