Hi everyone. I have assessments data for 20 individual across 12 months.
For each month, the assessments are recorded in a workbook where a specific work sheet is assigned to a specific individual.
Now I need to consolidate all the scores for the 20 individuals for 12 months in a single work sheet.
The sheet I need to fill out is laid out with 5 columns for an individual for a month - 3 columns assessment score (3 assessments in a month for each individual), 1 column to show average score and 1 to show %. So I have a total of 100 columns for 20 individuals for a month and so a total of 1200 columns out of which I need to populate 720 columns across 84 rows (there are 84 questions) from 12 workbooks.
There are 84 questions per assessment that are scored and the scoring cells are in fixed cells, meaning the scores for all parameters for Assessment 1 will be in column H, scores for all parameters for Assessment 2 will be in column L and scores for all parameters for Assessment 2 will be in column O.
So if I need scores for a specific individual for Q65 in row B72 then I need to look at H72, L72 and O72 in the specific sheet assigned to that individual across all 12 workbooks.
Is there a way(formulae/function) to populate the consolidated sheet with the required scores from the 20 sheets in the 12 workbooks.
Hope my query is clear. If someone can tell me how to attach a sample file or image from a excel here it would make the requirement more clear.
Please help. If I go about doing this manually it will take me 4 days at the least to just populate the consolidated sheet.
For each month, the assessments are recorded in a workbook where a specific work sheet is assigned to a specific individual.
Now I need to consolidate all the scores for the 20 individuals for 12 months in a single work sheet.
The sheet I need to fill out is laid out with 5 columns for an individual for a month - 3 columns assessment score (3 assessments in a month for each individual), 1 column to show average score and 1 to show %. So I have a total of 100 columns for 20 individuals for a month and so a total of 1200 columns out of which I need to populate 720 columns across 84 rows (there are 84 questions) from 12 workbooks.
There are 84 questions per assessment that are scored and the scoring cells are in fixed cells, meaning the scores for all parameters for Assessment 1 will be in column H, scores for all parameters for Assessment 2 will be in column L and scores for all parameters for Assessment 2 will be in column O.
So if I need scores for a specific individual for Q65 in row B72 then I need to look at H72, L72 and O72 in the specific sheet assigned to that individual across all 12 workbooks.
Is there a way(formulae/function) to populate the consolidated sheet with the required scores from the 20 sheets in the 12 workbooks.
Hope my query is clear. If someone can tell me how to attach a sample file or image from a excel here it would make the requirement more clear.
Please help. If I go about doing this manually it will take me 4 days at the least to just populate the consolidated sheet.
