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!
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:
