Hi there,
I'm trying to consolidate all my information so that I only need to completeone excel spreadsheet and the other spreadsheet is automatically updated. And Ineed help with formulas as I'm not sure if what I am doing is correct or if it can be done.:Cry: Also as there is senstive information I cant post my spreadsheed.
On sheet “Training Results” I have a list of people whohave attended various courses the date they attended and the mark achieved:
Column A = Name
Column B = Course
Column C = Date
Column D = Mark
This information is captured (automatically using thefollowing =IF('[TRAINING RESULTS SHEET.xlsx]Sept2013'!C6=0,"",('[TRAINING RESULTS SHEET.xlsx]Sept 2013'!C6)) function)from the attendance register that I must send off to my HQ.
Now I want to have a worksheet for each course and anoverall worksheet.
The overall worksheet I will be able to see quickly if aperson needs to attend any specific course or whether they must attend a courseagain. (Some of our courses need to be done on a 6 month basis).
The “individual course sheets” will have the person’sname and repeated columns for date and mark.
Column A = Name
Column C = Date
Column D = Mark
Column C & D are copied 12 times (so that somecourses are run every month)
Now here is my list offormulas that I need help with:
1. I want to pull the info from “Training ResultsSheet” onto my “Individual Course Sheet” So I want the formula to look atcourse and pull it to the right sheet but at the same time be able to link itto the right name in Column A. Colum A (has got the information from a sheet inthe attendance register work book. ='[TRAINING RESULTS SHEET.xlsx]PE StaffProfile'!$A3 is the formula used). So that I have the number of dates and marksfor every time they attended the course.
2. Then for the Overall Worksheet. I only needthe latest Date and Mark that corresponds to that date. Now the individualcourse has 12 sets of dates and marks. The dates will be in columns C; E; G; I;K; M; O; Q; S; U; W; Y And Marks in Column D; F; H; J; L; N; P; R; T; V; X; Z. Iwas thinking of using the following formula but I need then to only look atevery second Column for the latest date =IF(MAX(c7:z7)=0,"",MAX(c7:z7)).So I need to know how I can do that
3. Lastly I need a formula to get the mark that correspondsto that date.
Any help would be appreciated.
I'm trying to consolidate all my information so that I only need to completeone excel spreadsheet and the other spreadsheet is automatically updated. And Ineed help with formulas as I'm not sure if what I am doing is correct or if it can be done.:Cry: Also as there is senstive information I cant post my spreadsheed.
On sheet “Training Results” I have a list of people whohave attended various courses the date they attended and the mark achieved:
Column A = Name
Column B = Course
Column C = Date
Column D = Mark
This information is captured (automatically using thefollowing =IF('[TRAINING RESULTS SHEET.xlsx]Sept2013'!C6=0,"",('[TRAINING RESULTS SHEET.xlsx]Sept 2013'!C6)) function)from the attendance register that I must send off to my HQ.
Now I want to have a worksheet for each course and anoverall worksheet.
The overall worksheet I will be able to see quickly if aperson needs to attend any specific course or whether they must attend a courseagain. (Some of our courses need to be done on a 6 month basis).
The “individual course sheets” will have the person’sname and repeated columns for date and mark.
Column A = Name
Column C = Date
Column D = Mark
Column C & D are copied 12 times (so that somecourses are run every month)
Now here is my list offormulas that I need help with:
1. I want to pull the info from “Training ResultsSheet” onto my “Individual Course Sheet” So I want the formula to look atcourse and pull it to the right sheet but at the same time be able to link itto the right name in Column A. Colum A (has got the information from a sheet inthe attendance register work book. ='[TRAINING RESULTS SHEET.xlsx]PE StaffProfile'!$A3 is the formula used). So that I have the number of dates and marksfor every time they attended the course.
2. Then for the Overall Worksheet. I only needthe latest Date and Mark that corresponds to that date. Now the individualcourse has 12 sets of dates and marks. The dates will be in columns C; E; G; I;K; M; O; Q; S; U; W; Y And Marks in Column D; F; H; J; L; N; P; R; T; V; X; Z. Iwas thinking of using the following formula but I need then to only look atevery second Column for the latest date =IF(MAX(c7:z7)=0,"",MAX(c7:z7)).So I need to know how I can do that
3. Lastly I need a formula to get the mark that correspondsto that date.
Any help would be appreciated.