Forum Discussion
Pull data between two dates from multiple tables
I cleaned up the formula in A7 visually (splitting it across multiple lines), but left it essentially unchanged, to make it easier to understand. The main problem is that it relies on the WEEKNUM function (calculating the week of the year), which would make sense if individual classes did not cover multiple calendar years. But as they do, a different technique is needed.
I believe what you want is this formula, in cell A21 in the attached workbook:=LET(tasks, SORT(VSTACK(TaskA, TaskB, TaskC), {1,2}), start_dates, TAKE(DROP(tasks, , 1), , 1), end_dates, TAKE(tasks, , -1), period_start, calendar_start, period_end, $D$1, FILTER(tasks, (start_dates <= period_end) * (end_dates >= period_start), "none") )When you have tested that and are satisfied with the results, replace the formula in A7 with the formula in A21, and delete the latter.
Some other changes I made (such as to row/column widths and to how Snapshot's freeze panes are defined) are inconsequential, and you can change them as you wish.
Also:- The End Date for class C-007 looks like a date value, but is actually stored as text (despite the Date formatting; compare the values shown in the formula bar for that cell and the cell beneath it). I verified that using a formula with the TYPE function. I'll let you review that cell's value and edit it as appropriate.
- The formula in D1 of Snapshot was unnecessarily complicated; I recommend instead
=calendar_start + 56although maybe you want to add 55 to cover exactly 8 weeks, instead of adding 56.
I can add the additional classes, but I want to add milestone dates as well. When I try to do so, I get a #N/A error.
By introducing milestone dates (between the Start Date and End Date columns of the dynamic named range TaskF only), you are changing the structure of the "tasks" variable within the A7 formula. The FILTER function within that formula then fails because it is then working on #N/A values that the VSTACK function generated as a result of the different structures.
You can see this difference in structure in the attached workbook, as shown by the results from A7 (where I have removed the reference to TaskF) and A21 (where I have included only TaskA and TaskF, showing results before any filtering).
Based on the large number of defined Task"x" ranges you have defined, it appears that this is the wrong approach, even before this difference in range structure is involved. Tell us if you see some value in having all those separate ranges, but I see a simpler, consistent structure in using one range (or better, one Excel table) for all the class-specific data.
That is demonstrated on the "Class Dates (2)" worksheet. Using references to the Excel table tblClasses (that I created from your data) makes for simpler maintenance, and simpler and clearer formulas (on "LRTC (2)").
The inclusion of milestone dates in columns C, D, and E of LRTC hints that you want to use those in conditional formatting also. The last selected class (G-002) on "LRTC (2)" has data that demonstrates one possible formatting scenario, using the following rules:
For additional consistency and clarity, I put the start and end dates of the period of interest into cells A1 and D1. The associated text is defined in two custom formats. (Besides, the calendar starts on or before what was previously called "Calendar Start Date". The calendar is coded to always start on a Sunday.)
Using tblClasses and changing some formulas, nearly all of the Name Manager entries can become unnecessary.
Its easier to add the new classes to the task "x" ranges. I have roughly 250 classes and when the next class year list comes out, there will be even more. Being able to easily see which classes need to be added or removed seems easier to do when they are arranged how I had them originally.I am adding the milestone data for conditional formatting, and I would say I would need 10 milestones for the longest class.