How to create a master list worksheet from lists of names on several worksheets

I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)

I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.

I've tried a few things to make it happen, but nothing has worked.

I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.

I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.

Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?

I've either forgotten or never knew so much about Excel that I don't know what else to try. If this master list wouldn't be so useful, I'd just give up, but it could improve things tremendously.

Please, does anyone have any suggestions? I'm sorry to say I'll need really precise directions.

Thanks, FDibbins. I do have Macros turned on, actually.

I'm working with a spreadsheet someone else created, which is why there isn't a master list to do extracts from. I love the idea of that - it sounds like exactly what I need, really. Can you give me any hints about how I would create that setup? Do I just copy and paste the data from each worksheet into a new one? Would I be better off creating an entirely new workbook.

Will try it then. I may have to come back for more advice when I need to do the extractions, but let me work on this first. Thanks!

Hi- how do you do extracts? That sounds like exactly what I want to do but I don't know what it means... :P

I have a workbook that contains several worksheets- each one is a different mailing list. I want to create a master mailing list that contains all data and when updated, updates the data on the other worksheets.

I know this thread is old but I'm hoping for some help!

Thanks!