What Formula to Use for Summary Data?

Good morning,

I am trying to do a summary tab for this tab: i.ibb.co/HDg56nh/image.png (I can't use the picture feature on this forum, get an error?).

I can't use vba as it will be online, so I am trying to do this: I want to scrape any days that has that red flag (I have conditional formatting, if it is blank it will be 100, if the red flag it will be 50, and if it's green 0) and just put a summary tab:


"Sep 2022
95A: Friday 2 - noel, Saturday 10 - noel"

I am trying to google it and think it can be done but I am struggling with it .

Any thoughts?

Thank you!
-noel

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done if for you this time.)

Please mock up manually what you want to see in the summary sheet. A non-working formula is of no use to us at all.

Eventually, I will have more and more tabs with calendars just like 95A and 95B but it will be the same thing,

summary tab:
95a
anybody with "red flags" (50) and the days and the person

95b
anybody with "red flags" (50) and the days and the person

etc..

and I don't want the empty days, you know?

Still using Excel 2010??

Try:
=LET(d,TOCOL('95A'!B4:H19),f,TOCOL('95A'!B5:H20),n,TOCOL('95A'!B6:H21),r,FILTER(d,f=50),CHOOSE({1,2,3},TEXT(r,"dddd"),TEXT(r,"dd"),FILTER(n,f=50)))

SEE FILE.

That is perfect! However as soon as I try to enable editing, it changes: =_xlfn.LET(_xlpm.d,_xlfn.TOCOL('95A'!B4:H19),_xlpm.f,_xlfn.TOCOL('95A'!B5:H20),_xlpm.n,_xlfn.TOCOL('95A'!B6:H21),_xlpm.r,FILTER(_xlpm.d,_xlpm.f=50),CHOOSE({1,2,3},TEXT(_xlpm.r,"dddd"),TEXT(_xlpm.r,"dd"),FILTER(_xlpm.n,_xlpm.f=50)))

and then changes the func to #NAME?

what is _xlfm and xlpn... etc?

There is a LOT of confusion (at least in my head) about what functions are available in which version of O365. TOCOL seems to be one of them.

Please go to File/account/About Excel and confirm the Excel Version.

Have you enabled updates?

Is it up to date?

What exactly does it say below the microsoft logo on that page?

Microsoft excel for microsoft 365 mso (16.0.13127.21842) 32-bit and yep it is enabled and up to date.. it shows up correctly and until I try to enable editing, then _xlfn.LET(_xlpm.d,_xlfn.TOCOL('95A'!B4:H19),_xlpm.f,_xlfn.TOCOL('95A'!B5:H20),_xlpm.n,_xlfn.TOCOL('95A'!B6:H21),_xlpm.r,FILTER(_xlpm.d,_xlpm.f=50),CHOOSE({1,2,3},TEXT(_xlpm.r,"dddd"),TEXT(_xlpm.r,"dd"),FILTER(_xlpm.n,_xlpm.f=50))) and I get an error when i try to change back to "=LET(d,TOCOL('95A'!B4:H19),f,TOCOL('95A'!B5:H20),n,TOCOL('95A'!B6:H21),r,FILTER(d,f=50),CHOOSE({1,2,3},TEXT(r,"dddd"),TEXT(r,"dd"),FILTER(n,f=50)))" I get, "There's a problem with this formula" blah blah blah..

Shoot, it's not there when I looked :/. Straight from tinv, today & transpose
-n

is TOCOL available in your function list?

I guess that it's not...

If it's not available... that's a serious pain in the @$$. I had it 90% there for non-O365 and deleted it when you said you had O365.

Sound of Glenn spitting feathers...

So, try this one.... Three separate formulae, but seem to be OK.

Phew. That was a tough one to crack!

We got there, though. I need some beer (it's almost 6 pm here, so it's not too early).