Forum Discussion

Iron Contributor

Feb 10, 2023

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?

Excel

mathetes
Feb 28, 2023
wcstarks

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.

mathetes

Silver Contributor

wcstarks

I am trying to understand the formula. It starts at A24 and ends at T2401. It appears that the beginning parameter moves down as new rows are inserted and the ending parameter remains the same (causing it to effectively move up)

The copy I have has different start and end rows, since you've been adding new rows in yours. Nevertheless, I think that I understand what's happening, and, yes, it is caused by your entering new rows at the top. It's also complicated, I think by the fact (at least on the copy I have) you have extraneous rows at the top AND a row at the bottom, rows that contain formulas, but not the data to which those formulas refer. You also have an extra cell at the very bottom that does an average of the Glucose column.

From a sheer data table point of view, my sense is that if you made this a cleaner table--i.e., eliminated those two incomplete rows and the Glucose average calc at the bottom--and started making all entries at the bottom, then the formula, once adjusted to the top and bottom, would automatically track with each new entry, each new row. And I believe the formulas would automatically cascade into the new rows as the raw data to which they apply is added.

So my recommendation would be:

first, eliminate those "incomplete" rows at the top and bottom, eliminate also the single cell that computes the average Glucose. [That latter can be done anywhere in the workbook; it does NOT need to be at the bottom of that column.]
second, adjust the formula in the Print Range sheet to the new top and bottom rows, whatever they are
third, try inserting and entering your new data at the top. IF that works, if the formula accepts that, then you're home free. If it doesn't, see if you can adapt your practices to entering new rows at the bottom of the table.

Let me know if those suggestions make sense, and the results.

wcstarks

Iron Contributor

Feb 28, 2023

HI. I deleted the extra rows at the bottom and reset the top and bottom rows. Then I inserted a new row3 at the top and went back to the report sheet. The top row incremented to 4, but the bottom row incremented by one staying inline with the new bottom row. I see no practical way of entering at the bottom. It is just too awkward to have to navigate there for each row I add. Then I would also need to have the time column part of the sort, which it is not at this time. Doesn't the use of the "$" help with keeping the formula inline with the growth of the rows? I am not very familiar with the meaning of the $ in formulas.

mathetes
Silver Contributor
Feb 28, 2023
wcstarks

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.
wcstarks
Iron Contributor
Feb 28, 2023
mathetes
You may recall that I needed row 2 hidden to preserve formatting when I add next to the header. I copied your new formula into my current version and tried it with row 2 hidden with no data and the report sheet seems to work OK anyway. Thank you. I have attached the current version to reference.
Diabetes Manager.xlsx776 KB
mathetes
Silver Contributor
Feb 28, 2023
wcstarks
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)
Diabetes Manager_resort.xlsx772 KB
wcstarks
Iron Contributor
Feb 28, 2023
I tried viewing and adding new rows at the bottom. I find it too awkward. When I open the file, I really need to see the most current at the top. I work with this several times a day, as you can see from the number of entries per day. I also often view it between additions. So, I really need to use the sheet in this sort order. However, I really appreciate the new "report" sheet. It will be very useful. Thank you.
mathetes
Silver Contributor
Feb 28, 2023
You are most welcome. It's been a privilege to work with you on it. Appreciate your patience as well.