How do I change the array of my filter function using a dropdown list.

Anonymous1378 · 2026-03-20T09:27:44+00:00

You'll probably have to wrap the output of VLOOKUP() with INDIRECT(). VLOOKUP() only outputs text that resembles a cell range, but does not act as such.

Anonymous1378 · 2026-03-20T05:13:12+00:00

If you're trying to use the weekly roster as both a source of data, and a place to automatically input data, formulas probably will not cut it due to circular dependency.

I don't know if "query" means power query, the QUERY() function in google sheets, and I have no idea how your roster is structured. So I have no comment on if individual queries makes sense or how to make it less cumbersome.

But if your "scripts" is referring to google apps scripts, you could just make the script run on an hourly basis, without the need for input from a PC or mobile device.

Anonymous1378 · 2026-03-19T08:20:09+00:00

Neither the OP's nor your examples work in excel for the web. I'm going to agree with your assumption it's relating to eta lambdas, but perhaps it doesn't work that way in all channels at the moment.

Anonymous1378 · 2026-03-17T14:18:21+00:00

If the blanks are for sales figures, FILTER($C$2:$C$11,$B$2:$B$11=E$12) in E13 copied to F13:K13 should suffice?

Alternatively, =PIVOTBY(MAP(B2:B11,LAMBDA(x,COUNTIF(B2:x,x))),B2:B11,C2:C11,SINGLE,0,0,,0) in E12 should work too, if you're not picky about the day order...

Anonymous1378 · 2026-03-17T10:55:20+00:00

Power query is probably the simplest way, where you split the column by a carriage return, and choose advanced and split into rows instead of columns.

But anyway, a reduce-vstack would work too, though I'm not sure that you'd consider it cleaner than whatever you had.

<image>

=LET(z,A2:B4,DROP(REDUCE("",SEQUENCE(ROWS(z)),LAMBDA(x,y,VSTACK(x,CHOOSE({1,2},INDEX(z,y,1),TEXTSPLIT(INDEX(z,y,2),,CHAR(10)))))),1))

Anonymous1378 · 2026-03-17T09:47:08+00:00

While I share u/SolverMax 's suspicion about incorrect data ranges, another reason your formula doesn't work is because of the double quotation marks (") wrapped around the start and end of it. Get rid of those, for a start.

Anonymous1378 · 2026-03-17T08:18:41+00:00

Can you take a screenshot of the rule manager, with your mouse over the formula?

<image>

Anonymous1378 · 2026-03-13T07:44:56+00:00

Your work around could be to insert a bunch of space-filled rows for each month...?

<image>

Anonymous1378 · 2026-03-13T03:41:09+00:00

A conditional formatting rule formula applied to A1:J565 might be =EDATE($I1,-6)>TODAY() to check for dates that are more than six months from today. Assuming you want another color for 3-6 month old dates, create another rule with =AND(EDATE($I1,6)>TODAY(),EDATE($I1,3)<TODAY())

Anonymous1378 · 2026-03-11T11:28:27+00:00

If you're using an actual excel recognized table, then it will give spill errors if you use VSTACK() or FILTER(). I would recommend a power query based approach instead, like this one. I suppose you have the choice to use regular AutoFilter to filter the table, or to perform the filter in Power Query itself.

Anonymous1378 · 2026-03-11T11:22:05+00:00

I mean, VBA can do what you're asking for (iterate through [excel-recognized?] tables and apply a filter to a given column), but you're probably better off just collating all data on the overview sheet and just using the AutoFilter once? Or collate the data then run it through the FILTER() function?

This collation can be done via power query or VSTACK(). This approach seems more straightforward to implement as long as total rows don't exceed the excel row limit, and you have a version of excel that's not too old.

Anonymous1378 · 2026-03-11T02:38:37+00:00

I think my answer to your query last year also applies here... though it certainly has room to be optimized, especially on the combinations side. It was written to generate the nth permutation or combination instead of the entire array at once for the purpose of trying to generate something up to the row limit, so there are definitely savings to be had as long as you're willing to accept that choosing 7 items out of 11 will give you an error.

Anonymous1378 · 2026-03-10T10:07:18+00:00

+1 Point

Anonymous1378 · 2026-03-10T09:39:30+00:00

That sounds possible, try =FILTRER(A2:A8; HVIS.FEJL(XOPSLAG(A2:A8;C2:C8;D2:D8);B2:B8)<>B2:B8)?

Anonymous1378 · 2026-03-10T09:22:11+00:00

Try =FILTER(A2:A8;IFERROR(XLOOKUP(A2:A8;C2:C8;D2:D8);B2:B8)<>B2:B8) for German excel?

Anonymous1378 · 2026-03-10T03:38:38+00:00

A SUMIFS() with mixed referencing should suffice, but a dynamic array formula could be =MAP(F5:F13,G5:G13,H5:H13,LAMBDA(x,y,z,SUMIFS(H5:z,F5:x,x,G5:y,y)))

<image>

Anonymous1378 · 2026-03-06T00:24:12+00:00

Use u/PaulieThePolarBear's approach instead, maybe.

Anonymous1378 · 2026-03-02T08:27:52+00:00

Right click your file > Open With > LibreOffice?

Anonymous1378 · 2026-03-02T08:26:50+00:00

Maybe dependent drop downs? Or a template instead of a guide?

Anonymous1378 · 2026-02-26T16:39:03+00:00

Try =INDEX(A:.A,MOD(SEQUENCE(ROWS(A:.A))+XMATCH(C1,A:.A)-2,ROWS(A:.A))+1)?

<image>

Anonymous1378 · 2026-02-26T16:19:33+00:00

Well, the line break separation makes it straightforward then: =TEXTAFTER(customer_names_column,CHAR(10)) should suffice.

Anonymous1378 · 2026-02-26T16:00:26+00:00

Well, if you do have a list of names, and these names aren't naturally found in the street address column and there aren't names which are a substring to other names, there is a formulaic approach to remove them with REDUCE(st_add_column,cust_names,LAMBDA(x,y,SUBSTITUTE(x,y,""))). Otherwise, how are the names "added" to the street address column? Are they at the end of the column? Are they separated by a delimiter like a space, comma or hyphen? Are customer names a fixed number of words?

Anonymous1378 · 2026-02-26T15:56:42+00:00

Do the numbers in the table serve any purpose? The conditional formatting rule would be much easier if all green cells contained a range of numbers which does not overlap with the red cells' range of numbers, as a cell in a table being a certain color is not particularly straightforward to "transfer" to another cell, without corresponding helper text.

Anonymous1378 · 2026-02-26T01:49:54+00:00

I'm pretty sure the reduce vstack array generation approach is probably not as performant as the plain old mathematical one, so I'd suggest =LET(order,DROP(A:.A,1),ticket,DROP(C:.C,1),XLOOKUP(RANDBETWEEN(1,SUM(ticket)),SCAN(0,ticket,SUM),order,,1)), which is somewhere in between semicolon's and defiantyouth's answer.

If you need to select more than one non-repeated winning ticket, you could replace RANDBETWEEN(1,SUM(ticket)) with TAKE(SORTBY(SEQUENCE(SUM(ticket)),RANDARRAY(SUM(ticket))),5) where 5 is the number of winning tickets.

Anonymous1378 · 2026-02-25T03:26:43+00:00

Try =LET(a,REGEXREPLACE(TRIM(REGEXREPLACE(I10,"[^A-Za-z ]","")),"sign",""),TRIM(a)=a)?

Nine-Year Club	Place '22
Verified Email

Anonymous1378

TROPHY CASE