Forum Discussion

CliftonHomecare's avatar
CliftonHomecare
Copper Contributor
Jul 27, 2021

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 ...
Formulas and Functions
mathetes's avatar
mathetes
Silver Contributor
Jul 28, 2021

CliftonHomecare 

 

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.

Resources