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.
The arrays in that formula are defined just as in the workbook you supplied; I made no changes there. As currently defined in Formulas → Name Manager, each array can have up to 999 rows (worksheet rows 2 thru 1,000):
and that number of rows can be readily expanded as needed. (You are not required to use dynamic named arrays such as TaskA, TaskB, TaskC, but consistency is nice.)
For help with your #NUM error, you'll have to show your work. Either attach your updated workbook (or a subset thereof) or post the formulas you now have in the Snapshot column A cell and for any names you added to/changed in Name Manager.
- I figured it out, thank you!!
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.