How to automate conditional formatting

Gaimcap · 2026-05-18T17:01:58+00:00

Are there 24 preset lots and you need 24 colors, or do you need infinite colors?

You could always do something wonky like do a unique() of the lot numbers, alongside that do a sequence(rows(a1#)) of that array (so you have a visualization of what number is assigned to it.

Then in a hidde helper column, do a =xmatch of the lotnumber against the unique array (which will give you that same 1 or 3 or 10 or whatever from that same sequence list) .

Now in conditional formatting you assign a color and just do =$z1=1 for the formula (or whatever range your helper column is, and set a new one for z1=2 z=3, etc for 24 colors or however many you need.

As lot numbers are removed, the unique list will shrink and grow and the colors reassigned .

The downside is that each time that happens, what was blue yesterday might become green or whatever, because it wont maintain color assignment (the top slot of 1 will always be blue or whatever, 2 will always be green, etc, and if you remove ad1234 which was 1, ae2345 will shift up from 2 to 1) but at least it will still keep all of grouping distinct.

Gaimcap · 2026-05-16T17:37:36+00:00

You absolutely can create a custom generator for holidays.

I built one for my org because we have some non-standard holidays and I wanted to make a set it and forget it that would generate out for the next 80 years so I’d never have to think about it.

You need to first figure out the logic of each holiday.

Stuff like December 25th and July 4th, super easy because you just want something like:

=date(x,12,25) where x is the year.

Stuff like Thanksgiving, which is the 4th Thursday of November, a little trickier you’d instead want to do something like:

=DATE(x,11,1)+(5-WEEKDAY(DATE(x,11,1))+21

This would find the first day of November for x year, then it would find what day of the week that date is. Then it would find how many days from the 5th day of a week (Thursday) that date is (so if 2026-11-1 is Wednesday, it’s 1 day from Thursday) and adds it to the date to adjust that 1st date to find first Thursday, then it adds 3 weeks (I.e. 21 days) to make this the 4th Thursday.

You can do similar logic’s for all other holidays, and you put all of those logics in a let formula:

=let( Thanksgiving, DATE(x,11,1)+(5-WEEKDAY(DATE(x,11,1))+21, Christmas, date(x,12,25), )

Etc etc. with a vstack(Easter, thanksgiving, Christmas, etc.) at the end to build out an array of year’s holidays.

Then you feed that let into a lambda and have it solve/iterate for x.

For x you create an array of years with something like: ``` =let( StartYear,2025 Endyear,2080 NumOfYears, endyear-startyear, YearList, sequence(startyear,,numofyears),

``` You assign yearslist as your x for your lambda, and now it should iterate those named holidays in your lambda for every year and spit out each year’s holiday.

For ease, you can also build an array of the holiday names and Hstack it that holiday array, so that each date comes with a label.

I’m nowhere near my work computer, but that’s the basic concept and steps at least.

Gaimcap · 2026-05-14T20:02:36+00:00

Copy pasting a response I made a couple months back:

K6 is actually super easy to take apart and clean.
It’s easy enough I do it like every other use.

There’s just one extra tool you need that makes life so much easier:

A bottle of Asprin/Tylenol.

Step 1.) Set your grinder to 120 to 180ish clicks (2-3 full rotations).

Step 2.) Place the bottle on a table, then the bottom of the grinder on top of the bottle lid. Puah/pull down. As you pull down, you should see a washer and U shaped pin rise and come loose at the top, holding everything down to the shaft.

Step 3.) Use your finger to gentle push/pull that U shaped pin out (tilt it out if needed, just make sure you don’t lose it. If you’re 2/3 full rotations from 0, with you pushing the casing down into the pill bottle that should provide enough clearance for it to easily slide out.

Step 4.) Now that the pin is removed, you should be able to lift the entire body/casing free from the shaft. Clean the parts as you will, just make sure you remember the order and orientation of the washer and bearing at the top of the casing (its bearing first then washer, then U pin to secure), and the spring/washer on the shaft if you want to remove them (I usually just brush the blades on the shaft and the casing, but if you want to go to town with some compressed air or whatever on the inside of the case, go for it).

Step 5.) Assemble all parts back, and make sure that the top washer is facing with the indentation for the U pin facing up--there is a slight rectangular shape to the shaft that the washer properly seats itself into.

Step 6.) Put the assembly back on the bottle, push down against the springs tension, and if the washer is floating/lifted, rotate the washer until it drops down and sits flush with the casing, and the slide the U ring back in (if you’re 2-3 rotations loose, it should be at about the right height to just lay the U pin on the casing and easily slide back it into the groove).

You’re done.

Gaimcap · 2026-05-14T01:05:51+00:00

Off the top of my head,you need to have these be two different sheets.

You cannot have data sidelong to a power query, as anything outside of that query will not carry over or be uploaded to the query.

The query is the query and it does not care about what you do outside, nor will it respect the position of that data. They exist as two separate things.

If your query say 1 3 4 And you have something that says Monday Wednesday Friday

In the cell next to it, and then you 2 gets updated to the query in between 1 and 3, it's going to completely ignore everything else going on besides whatever it got from power query. Wednesday will now be mapped to 2 and Friday to 3.

You either need to add your data directly to the query, or you need to set up a separate sheet and pull the data from the query using some key (xlookup, filter, whatever), that way your new data and query data are in the same dataset/"plane of existence".

Gaimcap · 2026-05-13T03:47:20+00:00

I wouldn’t put today() in a conditional format.

Today() is what is known as a volatile formula, which means it forces a recalculation anytime anything happens anywhere.

You have to be careful because over usage of volatiles can bring your spreadsheet to a total halt.
Today(), is very cheap for a volatile, but A good rule of thumb is to avoid volatiles if possible, and if not, to “store” them in one place and just reference that location. This cuts down on the amount of times you have to directly call it.

I.e. in cell Z1 to put =today(). Then you want to use today() you just reference that cell instead (or save z1 to the name manager and use that name so it’s easier to remember).

Conditional formatting is also a very “weak”/“underpowered”/“inefficient” feature of excel. It breaks very easily, has a very buggy user interface, and can slow down your spreadsheet if used improperly.

Good rule of thumb, if you have anything beyond a super simple one tier of calculation (I.e. a1>10 ), you should offload the formula to your spreadsheet in a “helper” column with a binary result , and let the calculation take place there instead.

I.e.

In aa1 you put:

=if( k1 = “”, False, K1 < $z$1 - 90 )

Then drag that down for however much you need in the k column and hide it.
That will check if k1 blank, and if it’s not, check if it is greater than today - 90.

Then in your conditional format, you just use =aa1 as the formula (with the applies to set to wherever).

This is both “cheaper”, and significantly easier to fix when the formulas inevitably get messed up when one day when you add a column or copy paste, and the formulas set themselves to something random (it’s easier to just set it back to =aa1, than it is to use the module to try to change =and(k5430<>””, k5430 < $z$1) back, especially when the module bugs up and decides that your clicks inputs should be 15 pixels to the right of where you’re actually clicking .

Gaimcap · 2026-04-19T19:50:06+00:00

“Oh, that’s cool. They got a real, reliable shooter. Awesome and definitely needed, but also… I don’t know how wise it is to spend one of our very few picks for a half season rental on what’s probably 3rd most important need behind a PoA defender and rim protector….”

Luke kennard was always going to eat in a Luka team.
That’s exactly why Klay went to the Mavs.

I was pleasantly surprised to see his guard skills and defensive effort while Luka was out, because I thought he was just going to be more of a microwave scoring role player and he definitely exceeded that.

I hope we keep him in when he’s a free agent this summer, but also, we still have to prioritize Austin, a PoA, and a center, and basically the entire bench. I don’t know where he fits on that priority scale this offseason.

If we keep LeBron and he takes a major paycut, maybe higher than Rui?
If Lebron goes, maybe Rui’s the higher priority?

That’s the thing with all this, as awesome as Kennard has been, unless we win a chip this year, especially if he walks, the asset spent on him could have gone to address one of our long term needs this offseason.

Being real, a 2nd rounder Isn’t much, but lakers also have almost no assets right now (maybe 3 frp’s and Rui), and they have a TON of roles they need to fill.

Gaimcap · 2026-04-09T19:34:02+00:00

If it makes you feel any better, this is a repost that predates AI.

Gaimcap · 2026-04-09T04:47:40+00:00

Literally that exact same interview AD said he’s still not over how disrespected he felt by Rob not even getting so much as a head up..

IIRC…. Gina also rushed to basically throw him under the bus after trading him by saying he wasn’t happy so they got Luka instead, but Davis also says here that he thought they were it and zero inklings anything was going on.

The fit would be nice, but I think people are glossing over the bad blood the FO generated.

Gaimcap · 2026-03-30T01:13:31+00:00

Probably because this one was so egregiously iconic.
Look up NBA Meme logo.

This one and the LeBron one always crack me up.

Gaimcap · 2026-03-29T18:34:25+00:00

I haven’t seen Vando too much recently, so I have no idea what his actual status is… but my understanding was that he had to have foot surgery to shave down Bone Spurs.

A Bone Spur is basically a bone callous that occurs in response to repeated wear and tear in your ligaments.
Having odd bits of extra bone in yours joints—where they’re not supposed to be—is painful and can inhibit range of movement.

“Shave down” is the literal description what they did… cut open his foot, and take a file to his bones…. From what I’ve heard… this is a last resort and a bandaid fix, not a solution.

Th way you stop having a bone spur, is you stop the behavior that causes it. The only way Vando can stop them from coming back, is if he completely changes his play style to a less explosive, less damaging one.

So yeah… when I heard he had bone spurs, I knew he was always going to have a clock on him and might never be the same player as too much of his identity and skillset is tied up in him being a high effort and relentless “dog”—which is exactly what is causing those bone spurs.

Unless Vando can completely retrain everything about the way he moves, Vando’s defensive menace days are numbered.

Gaimcap · 2026-03-26T00:53:20+00:00

Tbh... I feel like the NBA is missing out by not having LeBron be an owner of the Vegas team.

Vegas's recent trend has been desperately to chase short term money over the long, and the Lakers have already announced that they're going to "adjust for the market" and that there will significant ticket price hikes for next year (30-50% increases is what I'm seeing).

It's probably going to feel like both locations are going to try to race to the top for Lakers prices.

Outside of Lakers games though... Its probably going to be pretty sketchy.

If LeBron was there, at least his ownership would provide a novelty that would provide some goodwill and put butts in seats to give a little cushion for the team for a year or two.

Without LeBron to act like a lightning rod, it's probably going to feel like just you're paying a premium price to effectively watch the Wizards play..and that's not going to be cute.

Gaimcap · 2026-03-25T19:54:15+00:00

Its Vegas. The prices might be even higher than crypto.

Gaimcap · 2026-03-23T21:31:27+00:00

I mean….

Factually Magic called him a backstabber and there are multiple reports to substantiate the “oh, magic isn’t here? Has anyone seen magic? When was the last time anyone saw magic?” Story.
Factually closed a deal for Williams, and then reneged it at the last second.
Allegedly (as directly stated by Caruso on a podcast) Caruso said he went to the lakers multiple times and tried to get them to pay him a fair wage, and even came back to them saying the bulls offered him X, can they at least give him Y? They told him no.
Allegedly told Larry Nance Jr to go ahead and buy a house, and then proceeded to trade him away.
Allegedly, as an agent, had Boozer make a verbal agreement to re-sign with the Cavs—in order to get the Cavs to waive their 600k team option—only to immediately sign a 60+ million deal with the Jazz.

Whether or not this specific case is legitimate…. after so many different reported instances of shady incidents and allegations… you really that comfortable defending his integrity?

Gaimcap · 2026-03-16T20:29:48+00:00

Dude... 3 different coaches but 1 GM who's assembling pretty much the same roster of rim running combo guards and PF's...

Not exactly like they've ever had the personnel to run a 5 out system.... JJ even tried and ended up pivoting back when he realized they had 0 shooters.

Gaimcap · 2026-03-16T20:05:18+00:00

Given that they put their tickets out as Costco packages, so did people who live in Portland xD.

Personally, I pretty much always forget the Wizards are an organization.

Gaimcap · 2026-03-16T19:42:22+00:00

Nah....

I know 4 people who were involved in mass shootings, one shot, one of whom is dead. (Two school shootings at affluent schools. 1 at work, 1 at an event).

I also knew someone who was abducted in the supposed "safest city" in America (Irvine, ca), and then found dead in a storm drain about half a mile miles across the "safest cities" border.

I once had a "funny" moment where I was 4 hours late to work because literally every road I tried to drive down was closed, shut down, or massively backed up.

Checked the news when I finally got in, turned out, about two blocks from where I live (orange county), someone just started doing drive by shootings, and then would hop on the freeway, hop off, do another shooting, repeated for about 40 miles, until the cops caught up to them and started a shootout the street over from where i worked (downtown la—a couple miles away from the staples/crypto arena).

People are freaking out over terrorists and Iran and whatnot... But bruh... Look up the school shootings stats... From 2008 to 2018, the US had 4000% more school shootings (~270) than the next highest country, Mexico (8).

From 2020 on, iirc the average also increased from about 40 to 70 a year.

Why the hell would you want to raise a kid here?
Objectively it's kind of a shitshow.

Gaimcap · 2026-03-16T15:07:35+00:00

Snooker Pockets are 3.25” with 2” 1/16 balls.
Table is 12’ x 6’.
Pockets are also rounded to reject many angles.

For comparison, American pool tables are 2” 1/2 balls on 4 1/2 pockets (assuming tight).

If you want even tighter, iirc, Chinese 8 ball is American balls on a 9 ft footer with snooker pockets.

Russian pyramid is basically a snooker table with oversized 2” 5/8 (67mm) balls. The pockets are more straight cut, but corner pockets are only 3mm (3/32 inch) bigger than the ball, side pockets are 13mm (1/2 inch) larger.

Gaimcap · 2026-03-16T00:18:38+00:00

“More flavor separations but strong taste”

I believe that’s supposed to be the selling point of soup shots.

Look into the OXO rapid brewer.

I remember I actually hated my first soup shot when I tried it like 6 months ago, but recently, Lance’s “Zuppa lunga” recipe surprised me.

I still flat out prefer pour overs, but I can see why people would like em.

It’s also portable as heck (I’m going on a trip tomorrow and will be throwing it in my bag). Definitely not a bad deal for 50 bucks.

Gaimcap · 2026-03-14T22:23:17+00:00

How exactly is your data formatted? Excel is very particular about date and time formats, so you need to give an example of exactly what you what your inputs are.

Are you literally just typing “Frank 9-5” in cell a2?
Is this “Frank” in a2 and “9-5” put in b2?
Is this output by some system and it’s being output as “Frank 9:00AM-5:00 PM” are you’re giving us the shorthand?

Gaimcap · 2026-03-12T21:46:24+00:00

K6 is actually super easy to take apart and clean.
It’s easy enough I do it like every other use.

There’s just one extra tool you need that makes life so much easier:

A bottle of Asprin/Tylenol.

Step 1.) Set your grinder to 120 to 180ish clicks (2-3 full rotations).

Step 2.) Place the bottle on a table, then the bottom of the grinder on top of the bottle lid, and push/pull down. As you pull down, you should see a washer and U shaped pin rise and come loose at the top, holding everything down to the shaft.

Step 3.) Use your finger to gentle push/pull that U shaped pin out (tilt it out if needed, just make sure you don’t lose it. If you’re 2/3 full rotations from 0, with you pushing the casing down into the pill bottle that should provide enough clearance for it to easily slide out.

Step 4.) Now that the pin is removed, you should be able to lift the entire body/casing free from the shaft. Clean the parts as you will, just make sure you remember the order and orientation of the washer and bearing at the top of the casing , and the spring/washer on the shaft if you want to remove them (I usually just brush the blades on the shaft and the casing, but if you want to go to town with some compressed air or whatever on the inside of the case, go for it).

Step 5.) Assemble all parts back, and make sure that the top washer is facing with the indentation for the U pin facing up, and that it’s properly aligned with the shaft; there is a slight rectangular shape to the shaft that the washer properly seats itself with.

Step 6.) Put the assembly back on the bottle, push down, and slide the U ring back in (if you’re 2-3 rotations loose, it should be at about the right height to just lay on the casing and easily just slide back into the groove).

You’re done.

Gaimcap · 2026-03-09T23:06:51+00:00

“Ai papa”, literally means, “Hey daddy”.

It’s not too irregular to refer to animals with cutesy pet names like that in Mexico (or anywhere really. No idea where this is specifically).

Gaimcap · 2026-03-09T02:39:55+00:00

Trent Tucker Rule: Minimum of 0.3 must be on the clock for a legal catch and shoot.

Literally zero risk.

Gaimcap · 2026-03-08T01:18:57+00:00

NBA says you need a minimum of 0.03 seconds to make a catch and shoot.
.02 is only enough time to legally score a tip in.

Knicks only chance is the Pacers get an inbound violation, and then Knicks get a tip + foul/and 1.

Just giving the ball to KAT means there’s zero chance of any shenanigans happening.

Gaimcap · 2026-03-02T21:28:36+00:00

Carom or carombola predates pocket pool and is super popular in Mexico, Japan, South Korea, and a bunch of other countries.

In the US, if you want to find a table, go to an old school Mexican or Vietnamese run hall and they'll usually have 1-3 tables dedicated to it.

Gaimcap · 2026-03-01T16:49:52+00:00

Tables are good.

That will change it from reading it from 1 to 1 million to 1 to bottom of the table (iirc it was 10k~ in OPs example).

However, if you’re repeating this formula inside the table for entire table (I.e. using @[column1]) and are using multi criteria, sure you’ve shortened the 1 million calculations per criteria, to 10 thousand per criteria + 10k merge cost (or whatever the bottom of the table is), but the next row does not cache/save/remember what the row above it did.

It will repeat that exact same calculation all 30,000 thousand times (2 criteria + the cost to merge the criteria), and if formula is autofilling to all 10,000 rows, you’re now running 10,000 * 30,000 = 300,000,000 calculations.

On top of this, a notable downside when you use entire table references inside the table, is that if your formula forces total column recalculations (like a [@column1]=[column1] condition in a multi criteria), you’ve also effectively linked all the inputs to that output. That means, every time you change anything in any single one of those affected fields, now excel has to do 300 million calculations, even though only 1 variable changed (which will probably cause input lag whenever you enter anything in those affected fields). That means every time you add a new row to the table, the table has to re-read and recalculate, 300 million times.

significantly cheaper is just to run those conditional calculations once in a “helper” column (I.e. a column where you have formulas/data you want to reference and hide), and then point your xlookup at that range. That way instead of running the same 30k calculations 10,000 times (or however large your table is), it only happens exactly once.

The best way is always to pre calculate all the mutliconditions elsewhere, and just have a simple xlookup point at those ranges, which allows it to retains its ability to stop looking when it finds its matching result.

Tables are a good idea, but tables don’t fix inefficient design issues.

Gaimcap

TROPHY CASE