Hi,

I generally don't struggle when it comes to Excel and its functions, but someone asked me something the other day that I'm sure can be done but I can't think how.

I have multiple spreadsheets that have basically been created as forms that can be filled in, and I need to pull data from one field into a master spreadsheet to collate them all. The master file has a list of partners who are on our company rebate scheme with a unique identifier against each. The individual files to be collated are the exact same layout, and will have the unique identifier in somewhere, say cell A1 for argument's sake; then I need to match on this cell, to populate the sales target into the master file, but the target is in a different row and column, say D5.

Is there any way to either do a lookup from the master file to the individual files to bring back the contents of D5 where the master file has a match with A1? If this can't be done via a match, another option would be to generate a new file from all of the individual files, taking A1 and D5 from each and putting them on a new row in the same column of the master file, therefore generating a list of all of the details.

I'm sure it can be done somehow, but not sure if I'll need macros or to go into Access to do it? If I can't find a way of doing it, there are just over 100 forms that me and a colleague will have to copy and paste from to create the master file.

Hope I've explained the situation clearly enough without going on too much. Any help or advice will be much appreciated.