Forum Discussion
Print for a date range
I would like a feature that I can use ad hoc that will let me do a standard print of my worksheet table for a date range that I specify for each print request. Is that doable?
If you've gotten rid of that top row that had only a few formulas in it, as well as both rows at the bottom that were not true data rows, then the formula below should work, and keep up with new rows at the top. However, I think you'll find that it works best to insert the new row just above row 3, not at the very top. At the very top, it will format itself based on the header row above, which you don't want to have happen.
This formula goes in cell A5 of the "Prt Date Rng" tab
=FILTER(tblQ1,(tblQ1[Date]>=Start)*(tblQ1[Date]<=Finish),"No Rows")
What you'll see there is that it no longer refers to the cell addresses, but rather to the table by name and the Date column by name.
Note: I've attached a slightly updated version of the older workbook, with the table modified (and an empty row waiting for you) and the new formula.
Regarding the $ symbol in a cell reference ( $A$3, $A3, A$3 😞 What these do, and it's significant, is to render the letter or the number (the column or the row reference, respectively) absolute rather than relative. Which means, as you copy a formula elsewhere, the parts of a cell reference that are absolute don't change, where the relative ones do, relative to the new cell. Here's a more complete description of how that works.
I'm pretty sure that what you're describing is doable. HOWEVER, exactly what would be the best approach may depend on getting some more details. For starters:
- What does the table look like (what are the columns [presumably date is one of them])?
- How frequently are you making these print requests?
- How many such requests each time you do it?
In other words, if those questions don't resonate with you, describe more fully the setting (ie. the business setting), what the table is all about, what your printed outputs are used for, etc.
I have attached a copy of the WB table (Sheet Q1). I want to be able to print it a couple of times a month. I would usually specify 2-3 ad hoc ranges to print at a time for comparisons. I imagine I would make a separate print request for each range. The ranges will usually include weeks up to a month or more at a time.
I have specified the "Print Area" to print all of the table columns when I select to print. In the print function, I can select page numbers to print, but when I want to print a date range, I have no idea the pages the range would include.
You don't say what version of Excel you have. The solution I've written here requires Excel 2021 or newer, in order for the two dynamic array functions--SORT and FILTER-- to work. Here's the formula in cell A5 of the new sheet I created in your workbook. You pick the start and end dates, in cells A1 and A2, and this filter pulls all of the rows meeting those two criteria.
=SORT(FILTER('Q 1'!A3:T2380,('Q 1'!A3:A2380>=Start)*('Q 1'!A3:A2380<=Finish),"No Rows"))
Basically this is a simple formula. All it says is FILTER the entire set of data from the table on the Q1 sheet where the date is greater than or equal to Start and less than or equal to Finish. (Note: I applied Names to the contents of A1 and A2. This makes the formulas easier to understand. That hyperlink will take you to an explanatory page, if this is a new technique to you.)
I also sorted these in date order.
I notice, by the way, that you're actually doing some calculations in the very bottom row of your table. In general that is NOT a good practice. If you hadn't done that, the formula above would read even more clearly because instead of A3:T2380 it would say something like @Date:@Comments and @Date>=Start
I would very seriously recommend you take those formulas away from that bottom row, put them at the top, and then add new data rows at the bottom. That is the "normal way" to maintain a data table. At the very least, do those calculations at the top (off site, so to speak, above the headers of the table), so that the table is nothing but tabular data. If you'd like, I will do that for you, but not without your permission.
