Data Table function inadequate?

[TomOhio]

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!!

Tom

[Bob Phillips]

I think seeing the workbook would be a big help.

[TomOhio]

Thanks for the reply.

I have created a sample of what I'm trying to do and have attached it.
I appreciate the help on this!

Tom

[TomOhio]

Bump. Any help is appreciated.

[haru]

Is this what you were looking for?

I changed, your sum row using the sum function so you won't get an error when load is empty.

Remember, if this solved your problem, edit your Title with [SOLVED] in front of it.

[TomOhio]

Sorry... no. That error occurred only because this is a sample and I didn't debug it. What I need are formulas in all the total spots that give me the totals for each row as if the correspnding NAME were used.

[haru]

How's this? One question, why even have a load cell? With the formula in the total column, you can sum the total without using the load cell.

something simpler would to just get the total of the common values, ea. 11100 and add it to the sum of the values 1 - 3.

Please Login or Register  to view this content.

[TomOhio]

I guess I'm not explaining myself. These are NOT the functions in my actual sheets. The mathematical functions in the sheets are not the issue. These simple add functions are there to simply show that operations occur.

What I need, and ALL I need, are functions in the dark green TOTAL cells that each assume that the LOAD value is equal to each TOTAL's corresponding NAME. I created the LOAD cell here only to simplify the demonstration of how the entrance of a single value allows, using the VLOOKUP, entrances into all the variables cells.

Thanks again for the attempted assistance, but I don't need a simplification of the operations I'm displaying. They are there only for demonstration purposes.

[Kyle123]

I think the example you've posted has made your issue seem trivial so I find myself agreeing with haru. I think however your workbook is much more complicated, let me see if I have this right.

1. The values in your workook are not in a table as per your example, but could be in any number of sheets
2. The NAMES refer to named ranges in your workbook and are not just random letters
3. How is the base value calcualted? in this case 11100

If the above assumptions are correct, then I think the same principal would still apply, i.e SUM the named range and add it to the base value. IF this isn't the case, it might be useful to post a workbook that matches the data layout you have, it is very difficult to suggest solutions entirely on principles that are difficult to convey.