Feb 10 2023 10:26 AM
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?
Feb 10 2023 10:45 AM
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:
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.
Feb 10 2023 10:58 AM
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.
Feb 10 2023 05:29 PM
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.
Feb 11 2023 12:19 PM
Feb 12 2023 08:01 PM
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.
Not sure what "z to a based on date only" means. Or was it a typo? In any event, all you need to do is remove the SORT from the formula (along with the first and last parentheses) to have the sort be in the order of your basic raw data. I've done if for you in the attached revised file.
You apparently put the formula into the comments column. When I enter data there it destroys the formula.
No, I put the formula ONLY in cell A5. FILTER is what's called a dynamic array function--so one instance of the formula pulls in the whole array that meets the criteria, and "spills" that array to the rows and columns needed, so long as there's nothing in one (or more) of those target cells. When you enter something (or something is pre-existing) in one of those cells, it generates a SPILL error. Don't enter data into that area. Enter data into your "raw data," not the output of 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.
So you're doing manually what Excel could do for you. l get why you don't enter things in the top row; I wouldn't either. But I recommend you familiarize yourself with the SORT function that I just removed from the formula on the "output" page, so at least you become familiar with how Excel could do the sorting for you. You're not obligated to use it, of course. Here's a link to an excellent YouTube video that explains SORT, UNIQUE and FILTER. It's where I first heard of the functions a little over a year ago.
Yesterday, I discovered an feature called "Get Data from Table/Range" when I right click on a cell. Does this do something similar?
I don't know, and don't see that happen when I right click on a cell. I'm on a Mac, so perhaps that's why I'm not seeing it. There are so many ways (all of the LOOKUP functions, for example; and INDEX and MATCH) to get data from a table or range...... FILTER is perhaps the newest, and (IMHO) one of the most flexible and powerful, but the others are useful in other situations as well.
Feb 12 2023 08:50 PM - edited Feb 13 2023 06:49 AM
The time field is left out of the sort, because I may not always put a time in the time field as I enter partial new data to save time, and fill it in later. If I have Excel sort for me, it would change the physical order I placed them in, if all times are not filled in. If I were to use the sort feature and include time, when adding the normal way, I would still need to go to the bottom of the table to enter the latest row, would I not? That would be awkward. With that understanding, I chose to insert at the top instead.
I hadn't noticed the range was on a different sheet. I thought I was still on Q1. Oh well. Thank you. This is great. I just have four questions related to the "Print Date Range". Why is the time formatted as a decimal number? Can it not retain its normal format? Is there a way to keep the empty fields blank instead of inserting 0s in them? Where is the formula. I presume it is hiding somewhere. I can only see it in grey in the function field when I click on an empty comment field. I guess I do not know where to look. It is not under view code for the new sheet. Sorry I am so dense. I find Access much easier to understand.
UPDATE:
Since your copy was a couple of days behind, I copied the new sheet to my current WB. I then tried out the filter for Aug, Sep and Nov. It seemed to work fine on each until I got about half way through the list on each of the filters, then the dates formatted to 5 digit numbers. See attached. Now, when I open the document it complains of an external source which it cannot update. I suppose that is because I moved the new sheet from your file to my file. I see the formula is referencing your version of the file. I am unable to change the formula as I don't know where it is. Does the formula need to use the full path? Can't it work in context?
Feb 13 2023 07:02 AM
The time field is left out of the sort, because I may not always put a time in the time field as I enter partial new data to save time, and fill it in later. If I have Excel sort for me, it would change the physical order I placed them in, if all times are not filled in. If I were to use the sort feature and include time, when adding the normal way, I would still need to go to the bottom of the table to enter the latest row, would I not? That would be awkward. With that understanding, I chose to insert at the top instead.
You are perfectly free to go ahead and do it that way if it works for you.
I hadn't noticed the range was on a different sheet. I thought I was still on Q1. Oh well. Thank you. This is great. You're welcome.
I just have four questions related to the "Print Date Range".
1. Why is the time formatted as a decimal number? Can it not retain its normal format?
A. You're aware that underneath the date and time formats are decimal numbers, I presume. FILTER is returning the "raw data" that is underneath the surface. All you need to do is format the field as date or time, as desired. In this case, you might find it most convenient to just format the columns A as date and B as time and do so all the way down to whatever row would be needed to accommodate the largest output you're going be doing.
2. Is there a way to keep the empty fields blank instead of inserting 0s in them?
A. To my knowledge, that's endemic to empty comment fields coming through the FILTER function; I almost left the comments column out of the FILTERed report for that reason. If the 0s bother you, you can put a space into each of the comment fields where there is no comment (make that the "default" entry. That way it will be read as "blank" instead of zero. I did it in a few of the cells for 9/30 to show the result. (Personally, I'd just tolerate the 0s, but if this report is for the use of other people, I can see why that may not be a valid route to take.)
3. Where is the formula. I presume it is hiding somewhere. I can only see it in grey in the function field when I click on an empty comment field. I guess I do not know where to look.
A. Well, it's kind of hiding in plain sight. As l mentioned in my last reply that it's entered only one place, and that's in cell A5. The results "spill" to all needed rows and columns; and the formula appears greyed in all of them except for the one at the top left of the array.
It is not under view code for the new sheet. Sorry I am so dense. I find Access much easier to understand.
A. If you find Access easier to understand, if you understand and write SQL code in that app, then you will definitely be able to come to grips with these dynamic array functions. I do recommend--quite seriously--watching the YouTube video I linked to in my last post.
UPDATE:
Since your copy was a couple of days behind, I copied the new sheet to my current WB. I then tried out the filter for Aug, Sep and Nov. It seemed to work fine on each until I got about half way through the list on each of the filters, then the dates formatted to 5 digit numbers. See attached.
As noted above, you will need to format the date column as date, time column as time, so as to convert those five digit and decimal numbers to the dates and times they represent. I'm assuming you know how that formatting works, in the "Home" toolbar.
Now, when I open the document it complains of an external source which it cannot update. I suppose that is because I moved the new sheet from your file to my file. I guess I shouldn't have done that.
I don't know how you did the copy/move, but yes...the FILTER function in the new spreadsheet was referring back to the other file, the original, on your OneDrive. I've edited it so that it now refers to the Q1 sheet. Again, watch the YouTube video to demystify how FILTER works. You might actually end up liking it more than Access. (Smiley face)
Feb 16 2023 11:15 AM
Feb 21 2023 02:03 PM
Feb 27 2023 10:30 AM
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:
Let me know if those suggestions make sense, and the results.
Feb 27 2023 04:53 PM
Feb 27 2023 06:52 PM
Solution
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.
Feb 27 2023 08:01 PM
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.
Feb 28 2023 06:36 AM
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)
Feb 28 2023 08:31 AM
Feb 28 2023 09:03 AM
Feb 27 2023 06:52 PM
Solution
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.