formula reference~muliple sheets
 

Hi, I need to generate a formula that will look at the data on sheet
one..reference a list on sheet 2 ..do some arithmatic then add the total of 3
cells together placing that total in its own cell.

sheet 1

Student ID Exam 1 Exam 2 Final Overall Grade
318-84-6039 100 93 79 87.40 B
332-03-6854 99 90 74 F
341-38-6902 63 51 43 F
362-80-6830 65 91 81 F
351-25-6606 74 65 58 F
303-86-6698 74 63 63 F
360-99-6115 63 50 43 F
342-45-6149 84 72 65 F
360-49-6615 78 65 58 F
331-38-6683 88 78 77 F
337-55-6535 72 64 62 F
300-60-6949 100 89 85 F
373-46-6412 99 88 84 F
397-44-6180 98 84 84 F
398-56-6736 100 92 86 F

sheet 2

Exam Type Weight
Exam 1 25%
Exam 2 25%
Final 50%

using these charts, i need to generate the data that would populate under
the overall column. What i need to do is reference the type, multiply the
value in the cell that corresponds to it by the appropriate weight which is
found on sheet 2 in the list. add the 3 cells together after multiplying them
by their weights and put that total in the column under overall.

ex 100*20%+93*30%+79*50% 87.40 would go under overall.

anyhelp on this would be appreciated
~Eelinla

formula reference~muliple sheets
 

On Apr 28, 6:14 pm, Eelinla wrote:
Hi, I need to generate a formula that will look at the data on sheet
one..reference a list on sheet 2 ..do some arithmatic then add the total of 3
cells together placing that total in its own cell.

sheet 1

Student ID Exam 1 Exam 2 Final Overall Grade
318-84-6039 100 93 79 87.40 B
332-03-6854 99 90 74 F
341-38-6902 63 51 43 F
362-80-6830 65 91 81 F
351-25-6606 74 65 58 F
303-86-6698 74 63 63 F
360-99-6115 63 50 43 F
342-45-6149 84 72 65 F
360-49-6615 78 65 58 F
331-38-6683 88 78 77 F
337-55-6535 72 64 62 F
300-60-6949 100 89 85 F
373-46-6412 99 88 84 F
397-44-6180 98 84 84 F
398-56-6736 100 92 86 F

sheet 2

Exam Type Weight
Exam 1 25%
Exam 2 25%
Final 50%

using these charts, i need to generate the data that would populate under
the overall column. What i need to do is reference the type, multiply the
value in the cell that corresponds to it by the appropriate weight which is
found on sheet 2 in the list. add the 3 cells together after multiplying them
by their weights and put that total in the column under overall.

ex 100*20%+93*30%+79*50% 87.40 would go under overall.

anyhelp on this would be appreciated
~Eelinla

Hi there,
Place this formula inB1 sheet 2 and type in the student # in A1

See if it works for you

=(VLOOKUP(A1,Sheet1!$A$2:$F$16,2,0)*0.2)+(VLOOKUP( A1,Sheet1!$A$2:$F
$16,3,0)*0.3)+(VLOOKUP(A1,Sheet1!$A$2:$F$16,4,0)*0 .5)

formula reference~muliple sheets
 

On Apr 28, 6:50 pm, "D." wrote:
On Apr 28, 6:14 pm, Eelinla wrote:



Hi, I need to generate a formula that will look at the data on sheet
one..reference a list on sheet 2 ..do some arithmatic then add the total of 3
cells together placing that total in its own cell.

sheet 1

Student ID Exam 1 Exam 2 Final Overall Grade
318-84-6039 100 93 79 87.40 B
332-03-6854 99 90 74 F
341-38-6902 63 51 43 F
362-80-6830 65 91 81 F
351-25-6606 74 65 58 F
303-86-6698 74 63 63 F
360-99-6115 63 50 43 F
342-45-6149 84 72 65 F
360-49-6615 78 65 58 F
331-38-6683 88 78 77 F
337-55-6535 72 64 62 F
300-60-6949 100 89 85 F
373-46-6412 99 88 84 F
397-44-6180 98 84 84 F
398-56-6736 100 92 86 F

sheet 2

Exam Type Weight
Exam 1 25%
Exam 2 25%
Final 50%

using these charts, i need to generate the data that would populate under
the overall column. What i need to do is reference the type, multiply the
value in the cell that corresponds to it by the appropriate weight which is
found on sheet 2 in the list. add the 3 cells together after multiplying them
by their weights and put that total in the column under overall.

ex 100*20%+93*30%+79*50% 87.40 would go under overall.

anyhelp on this would be appreciated
~Eelinla

Hi there,
Place this formula inB1 sheet 2 and type in the student # in A1

See if it works for you

=(VLOOKUP(A1,Sheet1!$A$2:$F$16,2,0)*0.2)+(VLOOKUP( A1,Sheet1!$A$2:$F
$16,3,0)*0.3)+(VLOOKUP(A1,Sheet1!$A$2:$F$16,4,0)*0 .5)

Here is an exelent write up on vlookup, print it out and read it

http://www.google.ca/url?sa=t&ct=res...7k9e_0mAYeHrxw

formula reference~muliple sheets
 

maybe im misunderstanding what your saying, but reentering the student
numbers is going to require more work than i want if at all avoidable. Also i
need the information to be able to be absolute so if the data changes in teh
look up sheet it can be adjusted across all 5 sheets by just adjusting the
list data.

"D." wrote:

On Apr 28, 6:14 pm, Eelinla wrote:
Hi, I need to generate a formula that will look at the data on sheet
one..reference a list on sheet 2 ..do some arithmatic then add the total of 3
cells together placing that total in its own cell.

sheet 1

Student ID Exam 1 Exam 2 Final Overall Grade
318-84-6039 100 93 79 87.40 B
332-03-6854 99 90 74 F
341-38-6902 63 51 43 F
362-80-6830 65 91 81 F
351-25-6606 74 65 58 F
303-86-6698 74 63 63 F
360-99-6115 63 50 43 F
342-45-6149 84 72 65 F
360-49-6615 78 65 58 F
331-38-6683 88 78 77 F
337-55-6535 72 64 62 F
300-60-6949 100 89 85 F
373-46-6412 99 88 84 F
397-44-6180 98 84 84 F
398-56-6736 100 92 86 F

sheet 2

Exam Type Weight
Exam 1 25%
Exam 2 25%
Final 50%

using these charts, i need to generate the data that would populate under
the overall column. What i need to do is reference the type, multiply the
value in the cell that corresponds to it by the appropriate weight which is
found on sheet 2 in the list. add the 3 cells together after multiplying them
by their weights and put that total in the column under overall.

ex 100*20%+93*30%+79*50% 87.40 would go under overall.

anyhelp on this would be appreciated
~Eelinla

Hi there,
Place this formula inB1 sheet 2 and type in the student # in A1

See if it works for you

=(VLOOKUP(A1,Sheet1!$A$2:$F$16,2,0)*0.2)+(VLOOKUP( A1,Sheet1!$A$2:$F
$16,3,0)*0.3)+(VLOOKUP(A1,Sheet1!$A$2:$F$16,4,0)*0 .5)

formula reference~muliple sheets
 

Hi,
What 5 sheets are you referring to??

formula reference~muliple sheets
 

there are multiple sheets with the same type of data on each.. if there are 6
classes there will be 6 sheets. If i have to input the student ID numbers
into a list for each it will take quite a bit of time.(lets say sheets 2-7
are class sheets and sheet one has the reference list)

i have a list similar to the one i posted already. i need to make a formula
that will allow me to multiple the test scores indiviually by the proper
%(which is based on the type and can be found in the list on Sheet 1) once i
get that amount i need to add the total of the 3 scores together and put them
into their own cell (in this case in the overall column) no matter how i try
i keep getting different errors. this needs to be able to carry over to all
sheets and copied down the entire column.

"D." wrote:

Hi,
What 5 sheets are you referring to??

formula reference~muliple sheets
 

Looking at your original post ..

In Sheet1,
Col E = Overall

Put in E2:
=SUM(B2*VLOOKUP(B$1,Sheet2!A:B,2,0),C2*VLOOKUP(C$1 ,Sheet2!A:B,2,0),D2*VLOOKUP(D$1,Sheet2!A:B,2,0))
Copy E2 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

formula reference~muliple sheets
 

An alternative, perhaps simpler in this instance, is to use defined ranges

In Sheet2, within A2:B4 are your weights:

Exam 1 25%
Exam 2 25%
Final 50%

Select A2:B4, then click Insert Name Create
"Left column" will be checked. Just click OK.

The above will auto-create 3 defined ranges:
Exam_1 =Sheet2!$B$2
Exam_2 =Sheet2!$B$3
Final =Sheet2!$B$4

Then in Sheet1,
Col E = Overall

Put in E2:
=SUM(B2*Exam_1,C2*Exam_2,D2*Final)
Copy E2 down.

You could use the same formula above
in all other sheets similar to Sheet1 within the book.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

formula reference~muliple sheets
 

Thanks. defining the range seems to have made the difference. Will be
something I think about more after working thru this issue.

"Max" wrote:

An alternative, perhaps simpler in this instance, is to use defined ranges

In Sheet2, within A2:B4 are your weights:

Exam 1 25%
Exam 2 25%
Final 50%

Select A2:B4, then click Insert Name Create
"Left column" will be checked. Just click OK.

The above will auto-create 3 defined ranges:
Exam_1 =Sheet2!$B$2
Exam_2 =Sheet2!$B$3
Final =Sheet2!$B$4

Then in Sheet1,
Col E = Overall

Put in E2:
=SUM(B2*Exam_1,C2*Exam_2,D2*Final)
Copy E2 down.

You could use the same formula above
in all other sheets similar to Sheet1 within the book.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

formula reference~muliple sheets
 

You're welcome. Good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eelinla" wrote in message
...
Thanks. defining the range seems to have made the difference. Will be
something I think about more after working thru this issue.