Combining sheets from more than one workbook


I share a workbook with my band memebers via email , each member then fills in a list of songs that they want to learn to play.

I then have to consilidate the list together so that I  can rank the songs , so we have a learning schedule.

the tedious part of the task is copying the lists from each member into a central sheet.

any ideas on how to automate this would great 



Look at the list of "Other questions" on the left of your screen. You will find inspiration, if not a solution. Perhaps you will find a solution there which could be tweaked to meet your requirements. Once you better know what you want - expressed in Excel terms - it will be easier to help you.
Variatus (rep: 813) Oct 21, '17 at 5:34 am
Try Google Docs - it's free and I feel like it should be easy to do what you want.

(not an Excel solution, but figured I'd mention it)
don (rep: 1247) Oct 23, '17 at 11:04 am
Add to Discussion



Do you share the workbook with a list or the members are free to make a list themselves? If they make the list themselves, can they use any cell/ row they like? Do you send them the workbook with the same name or different for each? Do you want the consolidated list just for once or more than that?
I think, it'll be easier for you if you send them workbook with a different name (or you may rename them later) for each of the members, telling them to make a list in a fixed range (different for each).
First, create a master workbook and several other mock workbooks (with the name you may send to your members) in a folder, e.g. "Songs" on desktop. For example, the master workbook may be MW.xlsx and others may be 1.xlsx (for member No. 1), 2.xlsx (for member No. 2), etc.
Next, tell member 1 to use the range B5:B100, range C5:C100 for member 2 and so on.
Open MW.xlsx and 1.xlsx in "Songs". Then in B5 in MW.xlsx type = and click on B5 of 1.xlsx. The link may look like:  ='C:\Users\User\Desktop\Songs\[1.xlsx]Sheet1'!$B$5        
Delete the $ signs and drag down. It will insert all the data in column B of 1.xls into MW.xlsx. You can do the same for all other workbooks.
After this, if you replace a workbook in the folder with the same name, the data in them will be updated in MW.xlsx once you refresh it by opening MW.xlsx and the workbook you want an update from.

If you send the members with a list, you can follow the same method making it like an attendence register to be checked.

I am a learner here craving for everyone's suggestions and guidance. Thanks!


Answer the Question

You must create an account to use the forum. Create an Account or Login