This hopefully has a simple solution...
I have approximately 20 files with between 1 & 3 sheets, each of which needs to be consolidated using a vlookup or similar without merging them into one file due to the size.
Each sheet is named as the department to which the data relates, and each file is named according to the manager responsible for the combination of departments.
My consolidation needs to "dump" the data from columns Z to AN but on different rows within each file, and different combinations for each sheet.
I have an index within column Z which is a simple equation of MAX($Z$2:Z101)+1 for row 100 etc... the max number of lines to be transferred is then populated into Z1 to determine the number of lines to be consolidated for that sheet.
What I thought of was to firstly look up all the Z1's according to the sheet name (I don't care about the file name for the consolidation), and then have a row for each sheet name / row number combination valid, but then comes the difficult bit. How can I set the dependency in a vlookup to reference the correct sheet & file path within the vlookup? I don't know a way of creating a dependency in the vlookup for file name like you might for which column to reference.
I hope that all makes sence... & thanks for any answers you come up with.
Mike
I have approximately 20 files with between 1 & 3 sheets, each of which needs to be consolidated using a vlookup or similar without merging them into one file due to the size.
Each sheet is named as the department to which the data relates, and each file is named according to the manager responsible for the combination of departments.
My consolidation needs to "dump" the data from columns Z to AN but on different rows within each file, and different combinations for each sheet.
I have an index within column Z which is a simple equation of MAX($Z$2:Z101)+1 for row 100 etc... the max number of lines to be transferred is then populated into Z1 to determine the number of lines to be consolidated for that sheet.
What I thought of was to firstly look up all the Z1's according to the sheet name (I don't care about the file name for the consolidation), and then have a row for each sheet name / row number combination valid, but then comes the difficult bit. How can I set the dependency in a vlookup to reference the correct sheet & file path within the vlookup? I don't know a way of creating a dependency in the vlookup for file name like you might for which column to reference.
I hope that all makes sence... & thanks for any answers you come up with.
Mike
