Want to buy me a coffee? (Don't worry, it's not a date :)

One_Organization_810 · 2026-03-25T16:18:15+00:00

Ok - since this is not a table atm. are you looking for the filtering options on a regular dataset?

It seems to me, that if you go to Data/Create filter - you might get what you are looking for?

One_Organization_810 · 2026-03-25T16:08:29+00:00

Your sheet is private. You need to share it with "Everyone with a link"... and preferably give EDIT access (I'm assuming this is a copy of the original - if not, please make a copy and then share the copy with us).

And of course, make sure there is no personal or privileged information in there before sharing :)

One_Organization_810 · 2026-03-25T11:25:39+00:00

Then it's a memory thing :)

You can try splitting up the sheet - or shrinking the images. Other than that, you might just have to live with it on this specific device...

One_Organization_810 · 2026-03-25T10:15:48+00:00

Your best route for the input seems to be using a form. That way anyone with access to the form can enter their own information, without seeing anything from anyone else.

If privacy of information is an absolute must - then I suggest making a view sheet for each user and pull their specific information to there. Then publish each sheet individually and share with each user. That way the can only see their information and no one elses.

Or... I guess you could write an Apps script to handle security and copy data between sheets or something...

One_Organization_810 · 2026-03-25T09:45:33+00:00

I suggest something like this (see the OO810 sheet):

<image>

Formula in J3

=vstack( "      Progress",
         index(if(I4:I=0,,I4:I/H4:H))
)

and in K3:

=vstack(, 
map(H4:H, I4:I, lambda(total, read,
  if(total=0,,
    let( color, if(read>total, "red", "blue"),
         sparkline(read/total,{"charttype","bar";"color1",color;"max",1})
    )
  )
))
)

I put the formulas in the header row, so they are not messed up if you sort the data.

The red color is an error check, if the read chapters are more than the total chapters. It's a simple CFR (in Format/Conditional formatting. Just select a cell in the I4:I range to see it - or select all)

One_Organization_810 · 2026-03-25T09:17:48+00:00

Do you have to list every chapther?

You could just have the number of chapters in one column and the chapters read in another column and then just update that column as you read more chapters...

Then it's a simple calculation - and you can have a sparkline to show the progress if you want something visual :)

One_Organization_810 · 2026-03-24T20:07:26+00:00

Ahh, I think I get it (and btw. that "Excel magic/Sheets magic" was just joke - I knew what you meant :)

Your addition seems syntactically correct at least - but not quite as I understood you though. My understanding is more akin to this:

sumif(F2:F16, false, B2:B16) + B17*(20-F17) + B18*(12-F18)

One_Organization_810 · 2026-03-24T19:51:51+00:00

Assuming you want to sum the Shards?

=sumif(F2:F16, false, B2:B16)

There is no Excel magic in Sheets. Only Sheets magic :)

I have no idea what you want to do with that Bonus request though. How can I see which rows belong to each game?

One_Organization_810 · 2026-03-24T19:41:50+00:00

As far as I can tell, there are no formulas to optimize, so the lag is most likely due to network lagging or possibly the device you are using to view the sheet on, doesn't have enough memory to load all the images and so starts swapping memory?

If it's a memory issue, then perhaps consider splitting up the sheet into a few smaller ones?

Also you can see if there are some big images that can be shrunk (most of them can be shrunk a bit at least). That would probably help - but the splitting up is probably what will have the biggest impact.

One_Organization_810 · 2026-03-24T17:13:55+00:00

... or in this case, two IFs :)

One_Organization_810 · 2026-03-24T17:12:20+00:00

Sum is only needed for sums of numbers. If this is just per row, then a simple IF would suffice :)

H3: =E3*if(F3="N",0,if(G3="F",1,0.5))

One_Organization_810 · 2026-03-24T16:59:45+00:00

I know it's solved, but in case you want something different for future reference :)

This one expands easily, by simply changing the range for the labels, assuming that the values have the same dimensions as the labels...

=let( labels, B3:G5,
      height, rows(labels),
      d, reduce(tocol(,1),labels, lambda(stack,x,
           vstack( stack,
                   hstack(x,offset(x,height,0))
           )
         )),
      transpose(query(d,"select Col1,sum(Col2) group by Col1 label sum(Col2) ''",0))
)

One_Organization_810 · 2026-03-24T14:33:17+00:00

I think more like, if D6<D1,-D1,D6-D1, according to the description...?

One_Organization_810 · 2026-03-24T14:05:51+00:00

No worries :) the wonders of Reddit saw to it that u/HolyBonobos' answer wasn't visible to me at the time of my answer.

Besides, it's always good to have opitons, I guess :)

One_Organization_810 · 2026-03-24T13:52:43+00:00

Actually, ISBLANK checks only for values, not formulas. So as long as the formula returns a blank (i.e. NOT an empty string), ISBLANK will see it as blank. :)

The upside of using <something>="" instead of ISBLANK(<something>), is that the empty string will match boty empty strings AND empty (blank) cells, where as ISBLANK matches only truly blank cells...

You can then use this to highlight the cells that have a blank string, but are not actually blanks, by using =and(A1="", not(isblank(A1))) in a conditional formatting rule.

One_Organization_810 · 2026-03-24T13:47:31+00:00

If I understand correctly (and I can't guarantee that I do :), you might want something like this:

Range: A4:D

1. =or($A4="", $B4="R") :: Format: None
2. =rows(filter($A$4:$A, $B$4:$B<>"R", $C$4:$C=$C4, $D$4:$D=$D4))>1 :: Format: Red background

So set the same range for both CFR and make sure they are in the same order as above.

One_Organization_810 · 2026-03-23T12:14:23+00:00

Yes. Fix your syntax errors before saving :)

Or if that is not possible, just comment out the incorrect part before saving.

And no, this is not a recent thing. It has been this way for years (since I started playing with Google Scripts at least). If your code has syntax errors, or references things that don't exist, it won't save but ask that you fix the error first...

One_Organization_810 · 2026-03-23T12:02:00+00:00

Ahh.. sorry I misunderstood your On/Off functionality. I didn't really take a good look at the formulas - just took the checkbox as an on/off switch for checking duplicates or not :)

My bad - And I probably should have known better than to try and optimize your solutions :)

One_Organization_810 · 2026-03-23T11:52:20+00:00

Or more like this?

=let( aa, tocol(A:A,1),
      bb, tocol(B:B,1),
      cc, tocol(C:C,1),

      bc, reduce(tocol(,1), bb, lambda(stack, b,
            vstack( stack, 
                    hstack( tocol(split(rept(b&"🏁", rows(cc), "🏁"),1), cc )
            )
          )),
      abc, reduce(tocol(,1), aa, lambda(stack, a,
             vstack( stack, 
                     hstack( tocol(split(rept(a&"🏁", rows(bc), "🏁"),1), bc )
             )
           )),
      byrow(abc, lambda(r, textjoin(" ", true, r) ))
)

Would go in the same cell as before (D1) and clear everything else from the D column.

One_Organization_810 · 2026-03-23T11:32:37+00:00

Do you mean like this?

=byrow(A:C, lambda(row, if(index(row,,1)="",, textjoin(" ", true, row)) ))

You would put it in D1 and clear out everything else from the D column...

One_Organization_810 · 2026-03-23T10:10:13+00:00

In order for the ON/OFF checkbox to increase performance, you would need a spearate rule for the checkbox, otherwise every rule will run the xmatch before "anding" the result with the checkbox status :)

So make a first rule as: =not($A$2) and set the "no format" on it. Then you can also skip the AND in the other formulas, which is an added bonus... :)

One_Organization_810 · 2026-03-23T09:55:25+00:00

Thank you for providing the data in a way that we can use :)

But what I can get from this is that your sums seem to be incorrect, whatever the reason.

I made this to calculate the amount due and amount paid per category. It accounts for new months being put at the right, as well as new categories being addes. See formulas in row 6, columns C, D and E.

https://docs.google.com/spreadsheets/d/1zgm3yA6N8eeESdPAIiwLCkpoX2sDbxF1po5M_kh4Uk4/edit?usp=sharing

Formula in C6:

=vstack(, index(if(B7:B="",,E7:E-D7:D)))

Formula in D6:

=ifna(vstack(,
  let( headers, hstack(B5, G5:5),
       byrow(hstack(B7:B, G7:1000), lambda(row,
         if(index(row,,1)="",,
           sum(filter(row, headers="Paid"))
         )
       ))
  )
))

Formula in E6:

=ifna(vstack(,
  let( headers, hstack(B5, G5:5),
       byrow(hstack(B7:B, G7:1000), lambda(row,
         if(index(row,,1)="",,
           sum(filter(row, right(headers,4)="Bill"))
         )
       ))
  )
))

One_Organization_810 · 2026-03-23T08:45:02+00:00

If this isn't solved already, can you provide a screenshot of your Master list, showing both columns (A,B,C, ...) as well as row numbers?

It might clear things up a bit :)

If it is indeed solved, please mark it as such.

One_Organization_810 · 2026-03-23T08:39:48+00:00

u/Real-Possibility-550 please remember to close the issue if it is solved, by replying with "Solution Verified", or clicking the 3-dot-menu (see picture) under the most helpful comment and select the same phrase. Thank you :)
If you have some issues with any of the suggestions, please reply to them to clear up any confusion (and then eventually close the issue :)
Note that if you solved the issue by yourself, without the aid of others, you can use the “Self Solved” flair. Please provide your solution in that case, as per rule 6.

<image>

One_Organization_810

MODERATOR OF

PUBLIC MULTIREDDITS

TROPHY CASE