?
Solved

link different cells in a worksheet to a table in excel

Posted on 2011-10-08
7
Medium Priority
?
179 Views
Last Modified: 2012-05-12
I want to link a range of cells to a table in excel.  For example the first columns data may come from the sum of cells A1, C36, D16.  Column two could be the sum of A36, B92,C18 and so on.  How do I do this?
0
Comment
Question by:Dier02
  • 3
  • 3
7 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 36935416
Hi, Dier02.

Could you post a sample file, please?

Thanks,
Brian.
0
 

Author Comment

by:Dier02
ID: 36935461
0
 

Author Comment

by:Dier02
ID: 36935467
There a range of scores and answers that have to be linked to a final sum then represented as a column graph.  If you look at A 100 you will see the questions that are grouped to give a result for that column.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:redmondb
ID: 36935562
Dier02,

Thanks. I'm finding it very slow to get my head around the sheet. A few question, please.

(1) Checking for understanding, are the following correct?
 - You are reporting under 4 Categories  - Knowledge and Understanding, Investigating, Communicating, Reflecting
 - There is a single Grade for each Category - A, B, C, D, E
 - The combined scores of a no. of Questions are used to assess each Category  -  Knowledge and Understanding  (Q2,4,6b,7), Investigating (Q1,3,5,6,8,9a), Communicating (Q2,4,6,7), Reflecting (Q6c,7,9)

(2) Row B26...
 - Is this Question 2?
 - Which cells are answers and which scores?
 - How much is the marker manually entering and how much will be automatically scored from the answers? (I'm assuming the tables in row 230 (?) and following will be used for this?)

(3) Having totaled up all a Category's points how is this converted to a Grade?

(4) Where a result has multiple grades (e.g. "Refines procedure by applying learning from investigation. (B/C)") how is it decided which Grade applies?

That's it ... for now!
Thanks,
Brian.

Thanks,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36935581
Dier02,

(5) Am I correct that where an answer is entered on the sheet, this is done by the marker, not the student? So, e.g., G51 is not strictly the answer, but rather an assessment of the answer?

Thanks,
Brian.
0
 
LVL 1

Accepted Solution

by:
cben earned 2000 total points
ID: 36940871
Not exactly clear about your full requirements but picked up a lot from your sheet. There is obviously a way to go. Have put suggestions into attached sheet with red cells.

Firstly, to answer your question about summing specific cells into a different table, my suggestion is that you use range names. If you Ctrl click each of the cells to be in the sum and then Formula > Range Names > Define the range for the name will be your cells. Make these into a formula by, at the front typing =SUM( and at the end ). Give it a clear name say Total_1. Then wherever you want to use that value type =Total_1. See cell E106
Some more suggestions on your spreadsheet.
 
1) Move the scores onto a separate sheet to aid switching, at least while you are working on it. You can hide the sheet so the marker cannot see it,
2) Instead of data validation lists in the box with commas use the list on the sheet (as you have done for some). Give the list a range name and use that to set up the validation, helps to track where you are. See cell I13
3) When you give numerical scores to these text evaluations use a look-up table. If you use vlookup then you need to have the items in alphabetical order, so a new table with a new range name is needed, See cell J13 for the formula and Scores sheet Factors_AB.
4) Use a lookup table again to translate the numerical scores to letter grades. This gives the consistency needed with less effort.

Hope this helps, it is easy for these projects, which are a mixture of text and numbers, to get hard to control.

 QCATScience-CBB.xlsx
0
 

Author Closing Comment

by:Dier02
ID: 36948288
Great work, thanks.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question