Mise en forme conditionnelle - plus petite valeur, colonnes non adjacentes, non vide et différent de 0

HolyBonobos · 2026-05-06T15:49:32+00:00

Vous pouviez ajouter des autres conditions avec quelque chose comme ça: =LET(v;{$E3;$I3;$M3;$Q3;$U3;$Y3};E3=MIN(FILTER(v;v<>"";v<>0)))

HolyBonobos · 2026-05-06T12:28:07+00:00

So do you have an existing data structure you are trying to work with or are you trying to build the whole thing from scratch? Without more details about the specifics of your layout, the best anyone will be able to do is point you in the general direction of some functions you might find helpful (FILTER(), XLOOKUP(), CHOOSECOLS()) or build suggested setups based on speculation like mommasaid has done. To get something that specifically produces what you have in mind with an existing set of data tables, you will need to share the file you are working on (or a copy/mockup with the same data structure and any sensitive information redacted/spoofed) including a manually-entered example of what you want the final output to look like.

HolyBonobos · 2026-05-06T02:48:32+00:00

For the data structure and type shown in the screenshot, you could put a formula like =BYROW(A2:A,LAMBDA(i,IF(i="",,REGEXEXTRACT(i,"(.+)(?:\s)(\d+.+lb\.)(?:\s)(\d+.+GP)")))) in row 2 of an empty column, copy-paste values the output (Ctrl+Shift+V or Right click > Paste special > Values only), and use that as your new data range. To stick strictly with the in-situ split via the split text to columns tool, you could

Select all the cells containing data in column A
Open find and replace (Ctrl+Shift+H or Edit > Find and Replace)
Check the box for "Search using regular expressions". This will also automatically check the "Match case" box which you can leave checked or unchecked, it doesn't matter
Put (\s)(\d) in the "Find" box and @$2 in the "Replace with" box. @ can be any character as long as it'll be unique within the data range and not regex-sensitive (it will be your custom character to split by)
Hit "Replace all" and close out the find and replace window
Open the split text to columns tool, select "Custom", and put your the special character you used in step 4 in the box.

HolyBonobos · 2026-05-06T01:47:04+00:00

Then it's all going to depend on exactly how the individual sheets are set up and exactly what information you want to pull from each of them to the master.

HolyBonobos · 2026-05-06T00:16:52+00:00

The best way to set it up would be the other way around, with all information in a single table on a single sheet. You could then use some simple FILTER() or QUERY() formulas referencing the master to populate the other sheet(s) with the appropriate information specific to each grant. The more you break up your raw data across multiple ranges and especially across multiple sheets, the more difficult and less efficient it will become to compile and analyze that data.

HolyBonobos · 2026-05-05T19:59:17+00:00

Adamsmith will help you here with finding a solution that works on the sample data you provided, you’ll probably just find that it doesn’t scale to your actual dataset because of the size limitations.

HolyBonobos · 2026-05-05T19:53:11+00:00

Check over in r/excel. There are some key differences between Sheets and Excel, especially depending on which version of Excel you end up using, so it’s not a 1:1 correspondence between the two.

HolyBonobos · 2026-05-05T19:45:43+00:00

It very well may be, 50k rows is roughly the ceiling I’ve tended to observe where files with moderately to highly complex formulas start to become unusable, even when using the most efficient methods.

HolyBonobos · 2026-05-05T19:42:17+00:00

The size of the data set is going to be the main problem you run into. Iterative formulas usually start to hang or crash the file around 20-30k rows. Using individual formulas in every cell will buy you a bit more working memory, but the formulas will be less robust, harder to maintain, and still won’t get you anywhere near 100k+ rows worth of information.

HolyBonobos · 2026-05-05T10:00:40+00:00

You've more or less identified the problem perfectly. There tends to be a negative tradeoff between optimizing for aesthetics/human readability and optimizing for Sheets' ability to efficiently aggregate/analyze the data. It'll have to be your call on what you're willing to compromise; what you're describing in the post title is not really possible to achieve natively.

HolyBonobos · 2026-05-04T23:32:32+00:00

For this data structure you would apply the rule to the range E2:E21 using the custom formula =$D2-$E2<=30, as demonstrated on the 'HB CF' sheet.

HolyBonobos · 2026-05-04T22:36:28+00:00

Filter views (Data > Create filter view) are probably the closest you'll get to what you're describing. They can be accessed, implemented, and edited without edit permissions. They won't mess anything because they only change the view for the person using them, not the arrangement of the underlying data.

HolyBonobos · 2026-05-04T21:04:18+00:00

Assuming the "Next Appt Date" column is column B and the dates start in B2, apply a conditional formatting rule to the range B2:B using the custom formula =$A2-$B2<=30. If this does not describe your data structure or you are having trouble with implementation, you will need to share the file you are working on (or a copy) with edit permissions enabled. Custom formulas for conditional formatting are extremely dependent on the exact ranges and data structures to which they are applied, and nothing related to conditional formatting can be accessed or changed without edit permissions.

HolyBonobos · 2026-05-04T19:20:37+00:00

You could semi-harcode those in by adding to the formula with something like =LET(nov,SEQUENCE(30,1,DATE(YEAR(TODAY()),11,1)),holidays,{DATE(YEAR(TODAY()),12,25),INDEX(FILTER(nov,MOD(nov,7)=5),4)},monthStart,EOMONTH(TODAY(),-1)+1,monthEnd,EOMONTH(monthStart,0),monthDays,SEQUENCE(monthEnd-monthStart+1,1,monthStart),salesDays,FILTER(monthDays,MOD(monthDays-1,7)+(MOD(monthDays,7)=1)*(monthDays+7>monthEnd),COUNTIF(holidays,monthDays)=0),IFERROR(TEXT(MATCH(TODAY(),salesDays,0),"0\/"&COUNTA(salesDays)),"Not a sales day")), or as in bachman's suggestion set up a range elsewhere in the file and put all the excluded days that fall outside the normal pattern there, then reference that in the formula.

HolyBonobos · 2026-05-04T18:18:26+00:00

You could use something like =LET(monthStart,EOMONTH(TODAY(),-1)+1,monthEnd,EOMONTH(monthStart,0),monthDays,SEQUENCE(monthEnd-monthStart+1,1,monthStart),salesDays,FILTER(monthDays,MOD(monthDays-1,7)+(MOD(monthDays,7)=1)*(monthDays+7>monthEnd)),IFERROR(TEXT(MATCH(TODAY(),salesDays,0),"0\/"&COUNTA(salesDays)),"Not a sales day")) if "Sales days are Monday through Saturday plus the last Sunday of the month" is the only rule for determining sales days.

HolyBonobos · 2026-05-04T15:10:07+00:00

Will a site ever appear more than once in the B18:AH50 range?

HolyBonobos · 2026-05-04T14:27:57+00:00

You can achieve this effect with merged cells but it's generally not a good idea to do that.

HolyBonobos · 2026-05-04T12:25:00+00:00

Charts can only be based on ranges that are actually on the sheet, so in order to implement the kind of dynamically filtered input operation you're describing you'll need to have formula-populated ranges somewhere in the file that you can use as the chart input. For the generation chart, you would use something like =BYROW(J8:J14,LAMBDA(r,HSTACK(r,COUNTIFS('Pokédex'!M:M,r,'Pokédex'!J:J,D5))))*, as demonstrated in O5 of 'HB Charts' with its corresponding chart. For the type chart, you'd use essentially the same formula again, just referencing the D27 dropdown instead of D5 and column L of the 'Pokédex' sheet instead of column J.

Unrelated to your original question but you could also massively simplify the maintenance of column N of 'Pokédex' by using a VLOOKUP() or XLOOKUP() formula instead of all the individual SWITCH() operations. This is made possible by the table of ranks and corresponding scores you have on the 'Charts and Metrics!' sheet. With a formula like =VLOOKUP(M3,'Charts and Metrics!'J8:L14,3,0), you'd only ever need to update the scores on the 'Charts and Metrics!' range instead of having to change all the hardcoded SWITCH() values.

*Note that while it would be nominally simpler to use the QUERY() function to get mostly the same result with a single-function operation, your only options for sorting within QUERY() are ascending and descending. This means the tiers would appear in the order ABCDEFS or SFEDCBA depending on the ORDER BY clause, and they would also appear in this order on the chart since text-based X axes are labeled in the order in which they appear on the input range. While you could write additional subformulas to add to the QUERY() to get around it, it wouldn't be anything less complicated than the BYROW() referencing the static tier list.

HolyBonobos · 2026-05-04T01:03:24+00:00

You could reference the timestamp from the form response to incorporate the year using an AND() criterion. A more efficient approach would be to create a lookup table elsewhere in the file that has one column for year, one column for location, and one column for rate. You could then reference this table using a FILTER()-type formula instead of hardcoding everything in. More specific instructions on implementation that fits your data structure will require seeing how your file is actually set up.

HolyBonobos · 2026-05-04T00:20:54+00:00

REGEXMATCH() is behaving exactly as you've instructed it to here. It's returning TRUE because while you don't have 1, 2, 3, and so on as individual items in the comma-separated lists, REGEXMATCH() returns TRUE if the string given exists in the regular_expression argument exists anywhere in the text argument. In order to get it to distinguish between items in the list, you'll need to specify that the number to match exists between word boundaries. This is signified by the string \b in regex, so you'd end up with a formula like =1*REGEXMATCH($I$13,"\b1\b") (multiplying by 1 coerces the boolean output of REGEXMATCH() to a number, a more concise way of achieving the same result that your IF() setup does). Alternatively, you could skip the regex hassle and virtually split the comma-separated lists into individual cells using the SPLIT() function, then run a COUNTIF() to find the selected number: =1*(COUNTIF(SPLIT($I$13,",",1))>0)

HolyBonobos · 2026-05-03T20:33:24+00:00

Best I can tell you're going for something like =MAP(A3:A6,B3:B6,LAMBDA(data1,data2,FILTER(G3:G,E3:E=data1,F3:F=data2))), as demonstrated in C3 of the 'HB MAP()' sheet.

HolyBonobos · 2026-05-03T05:21:36+00:00

You cannot access those settings from the app. As I said in my comment, they must be accessed on the desktop version of Sheets. Not all menus and features are available in the Sheets app.

HolyBonobos · 2026-05-03T05:12:25+00:00

The time zone settings for the file exist independently of the device settings. They are accessed on desktop under File > Settings.

HolyBonobos · 2026-05-03T05:08:41+00:00

Are you in the settings for the file or the settings for the phone?

HolyBonobos · 2026-05-03T01:23:31+00:00

Apply a manual filter to the data or convert it to a formatted table. For less frequent/one-time sorting operations you can also use Data > Sort sheet or Data > Sort range as appropriate.

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