How to change the array constraint? Google sheets

[Michele2022]

I think this Array_Constrain formula is supposed to limit the spreadsheet to 6 rows, but I don't see "6" in the formula. How can I increase the limit from 6 to 8 rows?

 

[KRice]

Welcome to the MrExcel board. It's difficult to offer much help without being able to understand what the formula is trying to do. It would be helpful if you would paste the formula in your post rather than a picture of the formula. Then others could copy it into a worksheet and analyze it more easily. It appears that you are forming an indexing array with:
ROW($I$6:$I$22)-ROW(INDEX($I$6:$I$22,1,1))+1
That will produce a sequential array of {1;2;3;...;17}, but it can be replaced and simplified with:
SEQUENCE(ROWS($I$6:$I$22))

You're also setting up some AND/OR conditions based on how E4 compares to I6:I22 and J6:J22, but I don't completely understand what is intended there. Whatever results in TRUE from the entire IF conditional returns values from the row-indexing array mentioned above...and you are taking the 4th smallest row index that is returned and then feeding that value as a row argument in an INDEX function to return just one value from B6:B22.

 

[Michele2022]

Thank you Kirk, for the advice and explanation. I am trying to edit the Content Calendar template from this site: Content Calendar Template. Please disregard my question above, as I will need to change it to 2 questions....1) On the Content tab, I added 2 more statuses that can be selected from the dropdown menu, but if I add an event in the Content tab with either of those 2 statuses, the statuses don't appear in the Calendar tab. 2) The template says that no more than 6 events can be entered in a day. Can the formula be edited to enter 8 events?

 

[KRice]

The structure of the month on the [Calendar] tab is such that only 6 rows are allocated for content, and the formulas used to return content for each day are hardwired to return just one item per formula...for the 1st, 2nd, 3rd, etc. items that meet the day-matching criteria. You happened to have selected a formula from the 4th slot on one of the days in your 1st post (that's what the "4" was for...and two rows below that was the "6" you were asking about). I think a better approach is to eliminate all of the formulas that return information from the [Content] sheet because those formulas are tedious to maintain, as each returns only one value, and even within a day mini-block, all of the formulas are different. The formulas can be upgraded with this replacement:
=ARRAYFORMULA(IFERROR(FILTER(Content!$B$6:$C$24,((A4=Content!$I$6:$I$24)+((A4>Content!$I$6:$I$24)*(A4<=Content!$J$6:$J$24)))>=1),""))
which spills all matching information from [Content] onto the relevant day in the calendar. Then you don't need to have separate formulas to return the 6th smallest, 7th smallest, and 8th smallest row indexes. A single spilling array formula in each day mini-block handles that automatically.

I've made the structural changes to the [Calendar] sheet and replaced all of the daily formulas with the one described above. About that formula...if it ever needs to be modified, it would be entered in the upper left of the calendar and pasted into every other column directly below the day number. After this is done for one week (the top row of the calendar), that entire calendar row from A:N is copied and pasted directly under the day numbers for each of the other weeks in the calendar block...and you're done. Here is a link to the revised template that will accommodate 8 rows (10 during one week):

Google Sheets: Sign-in

If you have more than 8 items to display on a day, select the top row of the calendar just below the day numbers (not the entire row of the worksheet, only the calendar portion), and insert cells with the "move remaining cells down" option. Insert enough rows to accommodate all of the content. This moves the spilling array formula down, but it avoids an error message that will occur if you try to insert a row within the spilling array range. Next, copy the formula that was moved down by the row insertion--and here I mean copy the formula WITHOUT the leading = sign. Do this for the first day of that week. Then go to the newly inserted row below the 1st day number for that week, enter an = sign and then paste the remainder of the formula. That should preserve the references so that no further editing of the formula is necessary. Delete any existing formula in that day mini-block and if any content matches that day, it should automatically spill. Then delete the remaining formulas in that week. Copy the formula you most recently re-installed (for the the 1st day of that week) and paste the formula under each day number (6 times).

I'm not entirely clear about your description where you mentioned adding "2 more statuses". The [Content] sheet has 6 "status options" as well as a scheduling table with a "Status" column heading...cells in that column use data validation dropdown lists that refer to the "status options". Are you saying you've increased the number of status options from 6 to 8?...or have you added some items to the scheduling table and associated each of them with one of the existing 6 status options?

 

[KRice]

Regarding the last paragraph in my last post, if you want 8 "status options" that's a little more involved, as it requires the addition of conditional formatting rules in several places. If I haven't messed it up, the version posted here does that: 8 status options and a calendar structure that accommodates at least 8 items in each day mini-block. In this example, the week of 15 May was expanded to accommodate 10 items each day. Then I noticed the array that should spill the results on May 20th produced an error. There are actually 11 items that need to be spilled on that day, and since all items cannot be delivered without overwriting existing content, an error is shown. There are two choices: 1) either add another row to the week (described in my previous post), or 2) limit how many items can spill from the formula (you'll be missing content, but the error goes away). To do the latter, the entire formula is wrapped with an ARRAY_CONSTRAIN function and then passed the arguments for the number of rows and columns to allow. In this example, I'm allowing 8 items (rows) to spill and they are 2 columns wide (because we are extracting information from Content!$B$6:$C$24...two columns...with the FILTER function):

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(FILTER(Content!$B$6:$C$24,((K22=Content!$I$6:$I$24)+((K22>Content!$I$6:$I$24)*(K22<=Content!$J$6:$J$24)))>=1),"")),8,2)

Here is the link to the 8 status options version:

Content Calendar Template_kr_v2

docs.google.com

Let me know what you prefer regarding the amount of information to display...would you prefer to NOT have missing information and adjust the day mini-block lengths to accommodate more than 8 items/day if that happens occasionally...or would you prefer to truncate the number of items at 8.

 

[Michele2022]

Regarding the last paragraph in my last post, if you want 8 "status options" that's a little more involved, as it requires the addition of conditional formatting rules in several places. If I haven't messed it up, the version posted here does that: 8 status options and a calendar structure that accommodates at least 8 items in each day mini-block. In this example, the week of 15 May was expanded to accommodate 10 items each day. Then I noticed the array that should spill the results on May 20th produced an error. There are actually 11 items that need to be spilled on that day, and since all items cannot be delivered without overwriting existing content, an error is shown. There are two choices: 1) either add another row to the week (described in my previous post), or 2) limit how many items can spill from the formula (you'll be missing content, but the error goes away). To do the latter, the entire formula is wrapped with an ARRAY_CONSTRAIN function and then passed the arguments for the number of rows and columns to allow. In this example, I'm allowing 8 items (rows) to spill and they are 2 columns wide (because we are extracting information from Content!$B$6:$C$24...two columns...with the FILTER function):

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(FILTER(Content!$B$6:$C$24,((K22=Content!$I$6:$I$24)+((K22>Content!$I$6:$I$24)*(K22<=Content!$J$6:$J$24)))>=1),"")),8,2)

Here is the link to the 8 status options version:

Content Calendar Template_kr_v2

docs.google.com

Let me know what you prefer regarding the amount of information to display...would you prefer to NOT have missing information and adjust the day mini-block lengths to accommodate more than 8 items/day if that happens occasionally...or would you prefer to truncate the number of items at 8.

Hi Kirk, thank you for the analyses! I will need to think about this and get back to you about the 2 options.

 

[KRice]

Hi Michele, I just wanted to check back that you were able to access the revised template I linked to in post #5. I think my sharing settings were incorrect, but you should be able to download it to your system. I found one glitch on the Calendar tab in cells P22:P24 (if I recall), but that's been fixed. Let me know if it has the functionality you sought, or if you encounter any issues.

 

[Michele2022]

Hi Michele, I just wanted to check back that you were able to access the revised template I linked to in post #5. I think my sharing settings were incorrect, but you should be able to download it to your system. I found one glitch on the Calendar tab in cells P22:P24 (if I recall), but that's been fixed. Let me know if it has the functionality you sought, or if you encounter any issues.

Yes, I accessed the revised template, but I didn't realize that I could download it because it was in "view only" mode. And so, I will try that. Thank you!

 

[KRice]

Let me know...I thought you'd be able to open it in Sheets and then File > Download to extract a copy to your system.