13th sheet combining the data from the preceding 12 sheets

**TheNewBoy** · 06-21-2014, 11:30 AM

Hi all, I'm new here and fairly new to Excel so be gentle with me please

I am currently building a yearly rota for about 200 staff over various locations in the business. Now, I have the rota so far that any updates the staff send me gets updated in the Master rota automatically over the 12 months (Jan to Dec). I have also (finally!) managed to get the days of the year to change automatically to reflect whether or not a date falls on a Sunday or a Monday and so on, as you change the year. This to me was a necessity as I want to build a rota that lasts a few years.

Now my problem is the following:

I want to build a thirteenth sheet that pulls the preceding months' data into one sheet showing a specific person's year divided into months. I have built the sheet to show the months, dates and days (days and dates change automatically with the year) and I know how to pull the data for one person into the sheet. However, I want to be able to use a pull-down menu so that I can change whose data I am looking at. For example if I select Person A, it should show his date for the whole year. If I select Person B it should then change to show his, and so on. I can put together a fairly usable pulldown menu using Data Validation and Lists but I don't know how to make it change the data in the page.

I have tinkered around with VLOOKUP and so on but have run into a brick wall. I am a complete idiot when it comes to VBA and I hope I don't need to use Macros to get this working.

I would really appreciate if someone can point me in the right direction.

**Glenn Kennedy** · 06-21-2014, 11:33 AM

I know I'm going to regret this question, 'cos I suspect your sheet will be a big'un... But can you post a suitably anonymised sample so we can play with it?

**TheNewBoy** · 06-21-2014, 01:03 PM

I shall make it a little smaller by removing s few sheets so it's just a few months' worth in there. How do I make it completely anonymous though? I trust all of you guys but I'm using a company laptop and don't want to get into any trouble...

**Glenn Kennedy** · 06-21-2014, 01:10 PM

Maybe filter on people's names and change Fred Bloggs to A, etc.

**TheNewBoy** · 06-21-2014, 01:15 PM

Done! I shall attempt to attach a version of the file that has just the very basics in it to keep it small. I have also removed all the sheets between January to September so it should be a small enough file but still retain the basic idea. (I hope)

Edited to say; I really appreciate the help guys!