Trying to automate a workbook via formulas

Brent

New member
Joined
Oct 3, 2013
Messages
2
Reaction score
0
Points
0
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.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
The way I would approach this would be to build a sheet to serve as a database, that looks like a CSV file. Each record would be on one row, using enough columns to store each record component. This sheet would be your single entry point, and from here you either use the other sheets to extract the information/summaries you need, or create a series of userforms in VBA. Also, you need to make sure that one column has a unique value (or key) to identify each entry.

I suggest this approach because we like to arrange and animate our spreadsheets, merge cells, etc. This is fine, but if you have lots of data to work with, keep that separate, and you will find it works better.
 
Top