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.
You may recall that I needed row 2 hidden to preserve formatting when I add next to the header.
I did not recall that, but it made sense as soon as I read it. It was the same reason I suggested using a row lower. And I'm glad that the formula works now and adjusts as new rows are added.
Nevertheless, on the attached I'm making one more effort to convince you to use the bottom row for new entries. You may not have realized you can "Freeze" the top of a workbook or worksheet, so that the column headings remain visible no matter how far down you are on the table. Here I've re-sorted the data so that the latest are at the bottom, so all you need to do is add a row. No "insert" needed.
I also took a stab at revising the formulas that, if I'm reading them correctly, give the averages for the last 7 or 90 days.....
You are, of course, totally free to keep going as you have, to ignore my OCD-ness on the topic. (smiley face)
- You are most welcome. It's been a privilege to work with you on it. Appreciate your patience as well.