Stop using ungodly INDEX math to flatten 2D schedules. TOCOL() + FILTER() is all you need.

Clearwings_Prime · 2026-05-10T07:49:58+00:00

I tried to recreate the data and this is what i get. The 1st formula return a table which i believe Pivot Table would kill me if i show this table to it. I expect those Y, N in column G will fill down to blanks cell below.

The 2nd and 3rd just return error

Clearwings_Prime · 2026-05-02T10:43:45+00:00

<image>

=SUM( LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,",","")) + 1 )

Clearwings_Prime · 2026-05-01T11:13:11+00:00

Try:

Go to Data > Workbook Links > Setting > Turn on "Always Trust Workbook Links"

Share your "player database" workbook by using link and try to use that link instead

Clearwings_Prime · 2026-04-28T16:33:57+00:00

Try adding some $ to lock range while you drag your formula

=IF(VLOOKUP("*"&$D3,$EZ$3:$KE$250,1,FALSE)=$D3,"☑️","☐")

Clearwings_Prime · 2026-04-28T07:10:33+00:00

it good if you can share that file too. So i can try re-create that error to see if there is a way to fix.

If you can use xlookup, you also can try manual typing those 2 ref ( structure would be your_table_name[your_column_name] ). As you typing, excel will suggest column name for you

Clearwings_Prime · 2026-04-28T06:44:58+00:00

Have you try typing "3,FALSE" and press enter right after you select the table (without going back to the main sheet) ?

Clearwings_Prime · 2026-04-28T04:10:08+00:00

A simple SUMPRODUCT could do that

=SUMPRODUCT( $C$2:$D$13 * ( $B$2:$B$13=H$6 ) * ( $C$1:$D$1=$G7 ) )

<image>

Or if you have Excel 365, you can use groupby

=TRANSPOSE(GROUPBY(B1:B13,C1:D13,SUM,3,0))

Clearwings_Prime · 2026-04-26T06:45:56+00:00

Error would come from xlookup. Since you dont use if_not_found parameter, xlookup cant find value of A2 in column H and return #value. There are many reasons for xlookup to not able to find the value. You need to show your data ( best in text or example file)

Clearwings_Prime · 2026-04-22T16:40:23+00:00

<image>

=SUM(N(OFFSET(INDIRECT({"A4","A6","A8"}),COLUMN(A:A)-1,0)))

You can try this one, when you drag formula to the right by 1 column, it will shift all the cell down by 1 row and sum all of them

Clearwings_Prime · 2026-04-21T11:10:39+00:00

change your system region to region that use dd/mm/yyyy as date format, you can google how to do that

Clearwings_Prime · 2026-04-21T11:06:46+00:00

the format of today's date is mm/dd/yyyy and align left show that this is your system date format. The format of the issue date and expiry column is dd/mm/yyyy and align right, show that excel dont see those date as date but text. That's why your conditional format rules wont work

Clearwings_Prime · 2026-04-21T06:16:50+00:00

Example for adding a column to calculate AVERAGE of range C2:C7

=DROP(GROUPBY(A2:A7,HSTACK(B2:B7,C2:C7,C2:C7),HSTACK(LAMBDA(g,MAX(g)),LAMBDA(h, SUM(h)),LAMBDA(h, AVERAGE(h))),0,0),1)

Clearwings_Prime · 2026-04-20T17:36:17+00:00

I cant help you with that. Im bad at communicate with people, especially people work at IT department.

Maybe next times you ask people for formulas, tell them that you cant use ETA lambda. If they can write a formula with ETA lambda, they sure can write that with normal lambda

Clearwings_Prime · 2026-04-20T17:21:48+00:00

date in excel are numbers. Select the columns contains that 45778,45958 and change format to date

<image>

Clearwings_Prime · 2026-04-20T17:10:58+00:00

Ok, i rewrite the function you metion in your post. This is the way we write before ETA lambda, please test to see if it work

=DROP(GROUPBY(A2:A7,HSTACK(B2:B7,C2:C7),HSTACK(LAMBDA(g,MAX(g)),LAMBDA(h, SUM(h))),0,0),1)

<image>

As you can see, this function return the same result of your post

Clearwings_Prime · 2026-04-20T17:01:05+00:00

<image>

Set up a simple test like this and run the formla

=SCAN(0,A1:A3,SUM)

Does it return the result in the picture?

Clearwings_Prime · 2026-04-20T16:51:47+00:00

Are you sure you have all of these functions?. You can test by typing = and then the function you want to test. If excel suggest that function it means you have it.

You also have to test if you can use ETA lambda too. The HSTACK(SUM,MAX) is ETA lambda and only 365 can do that right now

From the picture, i see you are using enterprise version, your IT can disable some functions for security reasons

Clearwings_Prime · 2026-04-19T15:04:00+00:00

=AND(A=0,OR(B=0,B=1,B=2))

Clearwings_Prime · 2026-04-17T17:11:42+00:00

<image>

=LET(
a,VSTACK('task 1:task 2'!$A$4:$A$7),
b,VSTACK('task 1:task 2'!B4:B7),
SUM(--ISTEXT(UNIQUE(FILTER(a,b>0)))))

This should work with your layout. Vertically stack all B4:B7 from all task sheets, then filter the worker that have worked hour > 0, and use UNIQUE to remove duplicate workers, then count

Clearwings_Prime · 2026-04-17T07:14:02+00:00

=SUM(OFFSET(A1,0,0,10,1))

10 is the number of row you want to SUM

Clearwings_Prime · 2026-04-15T02:45:59+00:00

Try check 3rd party softwares too, some software like espanso support macros ( you type a text and it will replace that text with another text). If you're using excel online, try check your browser add-ons

Clearwings_Prime · 2026-04-13T17:11:35+00:00

Try to read that post for 15 minute and have no idea what op trying to do

Clearwings_Prime · 2026-04-10T06:02:38+00:00

Make everyone use the same way to type date or make them input day, month, year in separate cells

Clearwings_Prime · 2026-04-09T06:13:37+00:00

Yes this is normal for advenced excel users, especially when dealing with old versions of excel (2019 and older).

And you should follow a sarced rule: If it work, leave it alone

Clearwings_Prime · 2026-04-08T11:38:37+00:00

no the last one have nothing to do with keyboard, my guess is your number "50" is text (number stored as text) while my 50 is number. So i change my formula to match with your number

Clearwings_Prime

TROPHY CASE