Extracting and managing due dates from multiple Excel spreadsheets

YorkshireMidge

New Member
Joined
Jun 3, 2017
Messages
5
Just looking for some ideas on a little problem.

The scenario is potentially dozens of Excel workbooks each of which have a number of important due dates against several line items which need to be tracked. Currently, you have to make a conscious effort to open them up and have a look at what is due - and it would be easy to miss something. The due dates tend to be relatively static for a few months at a time and the structure of the workbooks is static, and also very similar.

What I have played about with as a quick and dirty workaround, is have a master workbook which is simply a list of consecutive dates down column A into the future. For each of the workbooks being monitored, there is a COUNTIF formula in each cell which looks across a whole range of cells in the target workbook, and tries to match any of cells to the date in column A. Some conditional formatting blacks out all the zeroes - and highlights the cells with numbers, so it's a kinda crude diary that at least alerts you to look in the relevant spreadsheet when a due date is approaching to see what it is. When the master is opened, I can multiple select all the connected sheets and get the master sheet to open them all up in a matter of seconds and update. When the COUNTIF formula is done for each source workbook, it drags down the column nicely too.

Book1 Book2 Book3 etc.
20/10/21 0 0 0
21/10/21 0 2 0
22/10/21 0 0 1
23/10/21 0 0 0
24/10/21 0 0 0
etc.

My question is whether it's possible to do something more elegant whilst still maintaining individual Excel workbooks as the source (obviously a proper database in Access or whatever might have been a better starting point but isn't an option). What we have in mind is seeing if Excel could be made to push out these several due dates from each sheet into an Outlook calendar or something, OR for Outlook to pull the dates out the workbook and add them to the calendar. The problem is the methods I have seen used to do that sort of thing are a bit crude themselves and I want to avoid any nasty VB scripts too and keep things very simple for anyone to maintain with basic Excel skills - as some workbooks will be retired and new ones added over time. My other thought is whether there is a nifty third-party Excel reporting tool that can mine the data from multiple workbooks for me on the fly, and maybe mail reports out etc.

Any thoughts appreciated. Thanks!
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,605
Messages
5,765,385
Members
425,281
Latest member
tmoreira001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top