I frequently have the same data collected for several different semesters, and I need to consolidate it into a single worksheet. I'm pretty sure this step alone I can do with the consolidate function? But what I cannot figure out is the following: I can't just merge all the data points into a single sheet as they are in their original sheets/files--I need a new column to be created in the new master sheet (that contains all the data points from the different files) that gives something simple like the filename (if in a different file) or sheetname (if in a different sheet) for each individual data point, so I can track to which semester it belongs.

For example, suppose each sheet/file has a student ID number, the name of a course that they took, their course grade, etc. Suppose I named each file/sheet by the semester--e.g. fall_2014.xls. Then in the master file, I would want each of those individual records to including the data that was in the original worksheet (with the column names taken from any one of the original files/sheets, which I could specify manually if needed), such as ID number, course name, grade, etc, but also a final column at the far right labeled semester with fall_2014 added for the data points that came from the fall_2014.xls file.

I know that I could manually insert the column into every single individual sheet/file, but that is time consuming, and since I often have to do this for different datasets, I would really like to find a more automated solution. I'm comfortable with complex formulas, but pretty hopeless with VBA code.

Does anyone have any suggestions about how I might be able to find an more automated approach to doing what I would like? Thanks for your time!