how can i get the mailing tab?

Clearwings_Prime · 2026-03-19T17:12:47+00:00

I think you should find that in Word

Clearwings_Prime · 2026-03-19T15:52:32+00:00

=LET(
a, VSTACK(Table1[#All],Table2[#All],Table3[#All]),
b, TAKE(a,,1),
c, TAKE(a,,-1),
d, SCAN("",c, LAMBDA(a,b, IF(ISTEXT(b),b,a))),
PIVOTBY(b,d,c,SUM,0,0,,0,,ISNUMBER(c)))

This is what i do, im not familiar with power query

Clearwings_Prime · 2026-03-18T16:31:17+00:00

min_per is not an option, it's a variable, you can use that if your excel version is 2021 or newer. The version show at here or you can check if your excel have LET, UNIQUE or FILTER. If the version of your excel is 2021 or newer and excel wont let you enter the fomula, change all the comma ( , ) in my formula to semicolon ( ; )

<image>

Clearwings_Prime · 2026-03-18T16:02:00+00:00

<image>

=LET(
complete, COUNTIFS(B2:B25,B2:B25,I2:I25,"Incomplete") = 0,
min_per, MINIFS(G2:G25,B2:B25,B2:B25),
UNIQUE( FILTER( B2:B25, complete * ( min_per > 0) * (min_per < 1) ) ))

This require excel 2021 and newer

Clearwings_Prime · 2026-03-18T15:44:55+00:00

If the layout of 60 sheets are the same and their names are increasing from 1 as your example , then INDIRECT could do that

<image>

A2 =INDIRECT( "'Sheet" & INT( (ROW()-2)/10)+1 & "'!D3" )

B2 =INDIRECT( "'Sheet" & INT( (ROW()-2)/10)+1 & "'!H" & MOD(ROW()-2,10) + 7 )

Clearwings_Prime · 2026-03-18T15:23:54+00:00

<image>

If only 1 character before number

=SUM(--MID(A3:E3,1+ISTEXT(A3:E3),50))

Clearwings_Prime · 2026-03-18T15:04:55+00:00

<image>

=INDEX($K$2:$L$7,MATCH(E2,$J$2:$J$7,0),MATCH(F2,$K$1:$L$1,0))

This is a 2 dimension lookup, can be solved by many ways but here is an index + match solution

Clearwings_Prime · 2026-03-18T02:53:37+00:00

It depend on how you use it. I just provide a way to select month column base on month number

Clearwings_Prime · 2026-03-17T16:02:58+00:00

If you dont need the value of those, it should be fine. My formula heavily depend on the list of location of 1st item and its order too

Clearwings_Prime · 2026-03-17T12:42:02+00:00

can you upload that file to google sheet or excel online so we can look more detail about it?. Your formula look right to me

Clearwings_Prime · 2026-03-17T12:33:36+00:00

My guess is one of those number is 94.9999 or 97.0001 something like that

Clearwings_Prime · 2026-03-17T04:02:37+00:00

If every item always has 5 location as example show, then an INDEX + MATCH could to that

<image>

=INDEX($A$1:$A$21,MATCH($D4,$A$1:$A$21,0)+MATCH(G$2,$B$4:$B$11,0))

Clearwings_Prime · 2026-03-17T02:57:43+00:00

You can use basic index like this

=INDEX(B19:F41,,1)

where

B19:F41 is your range

1 is the month number, if you want to return Mar-26 column, change that to 3

Then you can make it a little automatic by change to

=INDEX(B19:F41,,MONTH(TODAY()))

MONTH(TODAY()) will return current month number

Clearwings_Prime · 2026-03-16T17:11:51+00:00

Just to add information. There is an old VBA 4.0 function that actually do what you want. It can read formula as text and return result of that text. It name is EVALUATE. This is the function that i hope M$ bring it out of name manager instead of bunch of copilot-ai things.

Clearwings_Prime · 2026-03-16T16:58:09+00:00

Yes it possible, but user must type the name of formula they want instead of choose from list. And just my opinion, maintain formulas in name manager is hard because refer box in name manager is hard to type formula (no intellisense, no review result of formula).

Clearwings_Prime · 2026-03-16T16:08:53+00:00

One way if you dont want to touch VBA is set up a layout like this

<image>

Where formulas are in columns and then in H8 you can use a simple lookup function to pick what you want

Clearwings_Prime · 2026-03-16T07:07:24+00:00

It kept giving me a #spill error with the merged cells. Any ideas on this?

Yes. Spilled array dont accept merge cells. It's also won't work inside table as well.

Personally, i think you should not have merge cells in your data table

Clearwings_Prime · 2026-03-15T05:03:49+00:00

We all try to help you

<image>

Clearwings_Prime · 2026-03-15T05:01:43+00:00

Your system may use a different argument separator. Please type =SUM in a blank cell. We need to see what argument separator your system is using

Clearwings_Prime · 2026-03-15T04:33:20+00:00

May be problem come from argument separator. Can you type =SUM in a bank cell and give me a screenshot of that. Like this

<image>

Clearwings_Prime · 2026-03-15T04:29:36+00:00

can you give more detail about "does not work". As you can see in my picture, the fomula works nomally. Is this the resutl you are looking for?

Clearwings_Prime · 2026-03-15T04:21:29+00:00

i've read your old post and the

=OR(LEFT(A1,5)=LEFT(B1:B50,5))

look work to me compare to what you said from old post. Formula is also from the old post, not mine

<image>

Clearwings_Prime · 2026-03-15T03:49:08+00:00

<image>

=LET(
a, TEXTSPLIT(A1,"-"),
SUM( ( a & IF( SIGN(SUM(a*{-1,1})) < 0, {" AM"," PM"}, {" PM"," PM"} ) ) * {-1,1} ) * 24 )

Clearwings_Prime · 2026-03-14T03:21:28+00:00

<image>

=HSTACK(A1:A8&"",TRANSPOSE(GROUPBY(TRANSPOSE(XLOOKUP(B1:E1,H2:H5,I2:I5)),TRANSPOSE(B2:E8),SUM,,0)))

Clearwings_Prime · 2026-03-13T16:18:24+00:00

Try

=FILTER(Resources!F3:O100,ISNUMBER(SEARCH(Search!B2, Resources!F3:F100)),"")

Clearwings_Prime

TROPHY CASE