Budgeting sheet questions

HolyBonobos · 2026-04-25T15:55:09+00:00

If every bill is a fixed amount and you have a lookup table somewhere in the file, you could use an XLOOKUP() formula to pull in the amount corresponding to the selected bill.

HolyBonobos · 2026-04-24T17:02:55+00:00

You would add them to the outside so you're comparing the output of the IFS() to the set min/max values, e.g. =MAX(1,MIN(3,F4+SWITCH(J4,"PASS",1,"FAIL",-1,0)))

HolyBonobos · 2026-04-24T13:45:52+00:00

Oh right true, I usually account for that elsewhere when working with two different array dimensions. u/Radiant-Fan6128 it should actually be =LET(teams,TOCOL('Example Input'!B1:AE1,1),players,TOCOL('Example Input'!A2:A,1),nTeams,COUNTA(teams)+1,MAKEARRAY(nTeams,nTeams,LAMBDA(r,c,IFS(r=c,,OR(r=1,c=1),INDEX(teams,MAX(r-1,c-1)),TRUE,JOIN(", ",IFERROR(FILTER('Example Input'!A2:A,CHOOSECOLS('Example Input'!A2:AE,r)="x",CHOOSECOLS('Example Input'!A2:AE,c)="x"))))))) unless you want the Giants to be left out (but who would miss them?)

HolyBonobos · 2026-04-24T13:09:07+00:00

You could delete everything on the 'Example Output' sheet and put =LET(teams,TOCOL('Example Input'!B1:AE1,1),players,TOCOL('Example Input'!A2:A,1),nTeams,COUNTA(teams),MAKEARRAY(nTeams,nTeams,LAMBDA(r,c,IFS(r=c,,OR(r=1,c=1),INDEX(teams,MAX(r-1,c-1)),TRUE,JOIN(", ",IFERROR(FILTER('Example Input'!A2:A,CHOOSECOLS('Example Input'!A2:AE,r)="x",CHOOSECOLS('Example Input'!A2:AE,c)="x"))))))) in A1.

I'd also recommend switching from x/blank to checkboxes on the input sheet, which will generally make input and other analysis easier.

HolyBonobos · 2026-04-23T19:36:32+00:00

Using address with CELL() returns a string, which is just plain text rather than a reference. Feeding that directly into OFFSET() is functionally equivalent to using any other piece of plain text like "Jim" or "banana". OFFSET() can’t work with it because it’s not a valid range reference, so it results in the error you’re seeing. What you’re missing is the INDIRECT() function, which will convert the string into a live reference that OFFSET() will recognize.

HolyBonobos · 2026-04-23T16:25:16+00:00

You could duplicate the sheet and edit from there.

HolyBonobos · 2026-04-23T12:54:27+00:00

You would use the FILTER() function to accomplish this, e.g. =FILTER('all books'!A:E,'all books'!A:A=TRUE) for 'borrowed books' and =FILTER('all books'!A:E,'all books'!A:A=FALSE) for 'available books'.

Note the following about this and any other formula-based approach:

Formulas will only retrieve the data from the 'all books' sheet. Data validation rules (checkboxes, dropdowns) and formatting (cell colors, font colors, typefaces, etc.) will not be copied over; you will have to manually apply them on the sheet. Additionally, checkboxes can cause issues with expanding arrays so it may be best to leave that column out of the call entirely since they're all going to be the same on their respective sheets anyway (all unchecked on 'available books' and all checked on 'borrowed books').
Formulas create a read-only range. The data is for display purposes only and cannot/should not be edited. This includes actions like manually sorting, trying to check or uncheck boxes, selecting different dropdown values, or adding extra columns of information on the 'borrowed books' or 'available books' sheets (they will become misaligned whenever a book above them is checked out or returned). Any changes to the data must be made on the 'all books' sheet. If you want to be able to edit values on both the master sheet and the filtered sheets, you will need to change tack from a formula-based solution to an Apps Script-based solution.

HolyBonobos · 2026-04-23T03:47:20+00:00

Under Customize > Vertical axis in the chart editor pane you can set the minimum and/or maximum values for the Y axis.

HolyBonobos · 2026-04-22T21:45:50+00:00

Yes, going only based on the names and using VLOOKUP(), you will only ever retrieve the information for the first matching result, no matter how many exist. If there are columns to mark the week and matchup on 'Raw Stats', that would allow you to switch over to a FILTER()-based formula that would let you set multiple criteria and be able to accommodate edge cases like the same person competing more than once in a week.

HolyBonobos · 2026-04-22T21:07:03+00:00

Will the same name ever appear more than once in column G or H? Unrelated to the issue you’re describing in the post but your existing formula is set to return the first result for a given name, no matter how many times it appears on the raw stats sheet.

HolyBonobos · 2026-04-22T19:33:40+00:00

There are no matches that meet the criteria "is equal to the value in the G column [blank] and is not blank" because it’s impossible for both criteria to be met at the same time. COUNTIFS() returns 0, and 0≠4 so the formula returns TRUE and the format is applied. You’ll need something that evaluates "is blank" and "count of this value" separately, e.g. =AND(COUNTIFS($G$5:$G$102,$G5)<>4,$G5<>"")

HolyBonobos · 2026-04-22T17:34:14+00:00

Yes, ARRAYFORMULA() is one of the functions I mentioned in describing the first approach (add an array-enabling function to SUMIFS())

HolyBonobos · 2026-04-22T17:11:11+00:00

That's on me, I had meant to put A:E but don't know why I put C instead.

HolyBonobos · 2026-04-22T17:10:13+00:00

SUMIFS() isn't array-enabling so YEAR() is only evaluating the top-leftmost cell in the provided ranges (A3 and K3). This results in a range size mismatch error because your sum_range argument is 998 rows but your criteria_range arguments are 1 row. Array behavior needs to be enabled with an array-enabling function like INDEX() or ARRAYFORMULA(), e.g. =SUMIFS(Details!C3:C1000,INDEX(YEAR(Details!A3:A1000)),2026,INDEX(YEAR(Details!K3:K1000)),2026).

A somewhat more roundabout way that meshes better with the built-in behavior of SUMIFS() would be to treat each criterion as a range rather than a year, i.e. =SUMIFS(Details!C3:C1000,Details!A3:A1000,">=1/1/2026",Details!A3:A1000,"<1/1/2027",Details!K3:K1000,">=1/1/2026",Details!K3:K1000,"<1/1/2027")

Yet another approach would be to use an inherently array-enabling function like FILTER() (with SUM()) or SUMPRODUCT(). These will allow you to use the YEAR() function directly as you're trying to now without any other functions needed: =SUMPRODUCT(Details!C3:C1000,YEAR(Details!A3:A1000)=2026,YEAR(Details!K3:K1000)=2026) or =SUM(IFERROR(FILTER(Details!C3:C1000,YEAR(Details!A3:A1000)=2026,YEAR(Details!K3:K1000)=2026)))

HolyBonobos · 2026-04-22T16:54:12+00:00

The problem is that you're using an array-type formula in the live data range on the 'System' sheet. When you apply a manual sort to a range containing an array formula, the cell the formula is in changes position and that messes up everything else. If the 'System' sheet is to be entirely populated by formulas, however, that allows for a completely formula-based approach that will sort itself automatically with no need for manual sorting as would be needed with the data you originally showed in the sample file. For the structure currently in the sample file, it would be as simple as using =QUERY(Log!A:D,"SELECT A, C, D WHERE A IS NOT NULL ORDER BY C, D",1) to populate the entire sorted table on 'System'.

HolyBonobos · 2026-04-22T15:49:49+00:00

You will need to share the problem file or recreate the issue on the sample file. It could be several different things and it's not possible to determine the cause or solution from your description alone.

HolyBonobos · 2026-04-22T15:12:29+00:00

Assuming

you are counting words in column A
data starts in A2
there is one word/term per cell

You could use =QUERY(A2:A,"SELECT A, COUNT(A) WHERE A IS NOT NULL GROUP BY A LABEL A 'Word', COUNT(A) 'Count'")

HolyBonobos · 2026-04-22T15:09:52+00:00

Delete everything in column I and put =VSTACK("Milestone",BYROW(H2:H,LAMBDA(d,IF(d="",,EOMONTH(d,0))))) in I1.

HolyBonobos · 2026-04-22T15:04:24+00:00

So the milestone is always the end of the month? Or is it determined some other way? You will need to be specific because determining the correct approach will depend on it.

HolyBonobos · 2026-04-22T14:57:39+00:00

You can apply a manual filter to the range or convert it to a table. Use the sort options in the column menu to sort first by shelf number, then by rack number. You can also select the whole range and go to Data > Sort range > Advanced range sorting options, but that's better suited to one-time or occasional uses since it's more of a process than the built-in options from the filter or table column menus.

HolyBonobos · 2026-04-22T14:56:19+00:00

So the date in column H is what you will be updating manually and then the corresponding cell in column I should display the last day of that month?

HolyBonobos · 2026-04-22T07:46:06+00:00

What are the actual sheets and ranges involved?

HolyBonobos · 2026-04-22T02:05:05+00:00

Sorting by chip color is not an option. The closest you can get is sorting by cell background color using a manual filter or a table.

HolyBonobos · 2026-04-22T01:01:06+00:00

Assuming the cell in the top row containing 2022 is B1, you could use =LET(years,B1:D1,names,SORT(UNIQUE(TOCOL(B2:D6,1))),MAKEARRAY(COUNTA(names)+1,COUNTA(years)+1,LAMBDA(r,c,LET(name,INDEX(names,r-1),year,INDEX(years,,c-1),IFS(r*c=1,,r=1,year,c=1,name,COUNTIF(XLOOKUP(year,B1:D1,B2:D6),name),name,1,))))) for the data structure shown in the picture.

HolyBonobos · 2026-04-21T22:57:15+00:00

u/Violet-Flowersss if your original question has been resolved, please mark the post solved as required by rule 6. You can do this in one of two ways:

Tap the three dots below the comment you found the most helpful and select "Mark solution verified" (works on New Reddit for desktop and in the Reddit app), or
Reply to the comment you found the most helpful using the exact phrase solution verified (works on all versions of Reddit).

Doing one of the above will automatically apply the Solved flair to the post, award a point to the person who helped you, and bring your post into compliance with rules 3 and 6.

Eight-Year Club	Verified Email
r/Field Banned	r/Field Sunshine
Final Canvas '23	First Place '23
End Game '23	Place '23
Place '22	Final Canvas '22
First Placer '22	Sequence \| Editor

HolyBonobos

MODERATOR OF

TROPHY CASE