| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula reference~muliple sheets
Hi,
What 5 sheets are you referring to?? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cross Reference between sheets | Excel Worksheet Functions | |||
| How to have a variable cell reference across sheets? | Excel Discussion (Misc queries) | |||
| create reference formula that looks through all sheets for matchin | Excel Worksheet Functions | |||
| Indirect and Address in Reference to other sheets | Excel Worksheet Functions | |||
| in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Linear Mode
