Results 1 to 2 of 2

Thread: Trying to automate a workbook via formulas

  1. #1

    Trying to automate a workbook via formulas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. 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.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •