Forum Discussion
Easy way to create a summary sheet with overdue dates on?
Hi,
I have a spreadsheet at work with different sheets for each staff member. It has dates of supervisions on - the last date they were done and when they are next due.
To save going through each one all the time to keep track, I'd like to create a front sheet with a summary of all staff and who is overdue etc.
Is there an easy way to do this? To flag the ones overdue etc? please help!
Abby
1 Reply
Yes it's possible. The INDIRECT function, in particular, comes to mind. Here's a link to a website that offers guidance on how that function works. https://exceljet.net/excel-functions/excel-indirect-function
It would be easiest to help you apply that concept if you could provide a mock-up sample--devoid of any real names of real people--of the way your spreadsheets are laid out.
Other than that, I can offer the attached--which is a totally different situation--but consists of a "Current Summary" sheet that pulls most of its data from subordinate sheets using INDIRECT. So it illustrates the method I have in mind.
Here's what a typical formula looks like: =INDIRECT($D5&"!"&F$1)
- In that formula, D5 is a cell in the summary sheet that contains a name that is also the name of the subordinate sheet (in your case, it could be the last name, or some other ID, of the person);
- F1 is a cell also in the summary sheet that contains itself a reference to a cell in the subordinate sheet (in your case it could be to the cells containing one of those dates; it would be important (crucial) that those subordinate sheets all be organized identically.
- INDIRECT puts all of that together to create a reference to Jones!B3, cell B3 in the sheet named Jones.
If your skill level at Excel is such that you'd like more help, please come back with that aforementioned mock-up example of your own workbook, and I or somebody else can create a working copy for you.
