Forum Discussion
Print for a date range
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.
- This basically does what I need. However, there are some issues. I need the sort order to be z to a based on date only. I need to see all the most current first. It is not convenient to have to go to the bottom to work and view the data. You apparently put the formula into the comments column. When I enter data there it destroys the formula.
When I insert rows, for convenience, I may insert a row in a day that may come after intervening rows which I have not yet entered, but that I will insert and fill in later.
Because I insert, I need the row next to the header to preserve the formatting and to be hidden. Without that, when I insert a row immediately below the header it takes the formatting from the header. I manually insure the chronological order within a day by how I insert rows.
Yesterday, I discovered an feature called "Get Data from Table/Range" when I right click on a cell. Does this do something similar?
