Combining staff holiday lists in a summary sheet - how to proceed?

[PixieNinja]

Hi There...

I'd like to create a summary sheet that reflects all staff on holiday in an office each day, collated from a number of sheets specific to each team within the office.

To be functional, I'd like the summary sheet to update each time the different team sheets are changed. I'd like any empty cells to be omitted, and for the summary sheet to list all of the people on holiday on each day.

Ultimately, I'd like to limit the ability of keying in staff for each team as the summary sheet approaches a max value - in the example sheet it's 15. So, if only two slots were available, each team would only have two cells to be able to fill in, any others would be greyed out. If all 15 cells were full, then all empty cells across all teams would be greyed out, and unable to have staff added to them.

I've no idea how to proceed with this. I've no idea if it's possible using formulas, and I haven't touched any VBA for years now.

If anyone can help I'd appreciate it.

[alansidman]

This can be accomplished using Power Query by joining each team on the Date and building a parameter query to show only the date you select. I have to run out now but will show you how later when I return. In the meantime, you may wish to look at the Power Query links located in my signature to get a better understanding of what I am referring to.

[PixieNinja]

Is anyone able to shed any light on how to do this using Power Query, or indeed any other method? I've been poking around it but can't figure out how to achieve the results I'm after. Didn't even know Power Query existed until this - seems very powerful, yet complex and mysterious enough to keep me clueless.

[alansidman]

See attached file for solution as described in my earlier post

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.