Search results

One way:=LET(a,FILTER(M4:N753,M4:M753>=D4),b,SCAN(0,TAKE(a,,-1),LAMBDA(c,d,c+d)),e,XLOOKUP(H4,b,TAKE(a,,1),,1),e) See cell I7 with that formula. Cell I4 has the named lambda equivalent with hints as to what belongs in the arguments:

See attached where I've done your number 1 requirement. Data validation in cell H1. Delete cell H1 contents or choose All from the dropdown to see all dates. Worksheet_Change event code in that sheet's code-module. (For debugging/explanation purposes, code called StepByStepWorksheet_Change takes...

=IF(B4<=1,0,DATEDIF(B4,TODAY(),"Y"))

try in row 3:=INDIRECT("'" & B3 & "'!F" & MATCH("Net Functional Area",INDIRECT("'" & B3 & "'!$E$1:$E$10000"),0))and copy down.

the visible cells it selects are from only those cells which were selected before you use the GoTo dialogue, but when only a single cell is selected before you use it, it assumes you want the whole sheet processing in that way.

Sparklines?:

That's a big ask. It would take me all morning to go through the steps. There is some manual editing of the M-code. The most obscure bit will be how the function fnMergeRows was put together. In the attached, I've duplicated that function (fnMergeRows (2)) and turned it back into a normal query...

Obviously, we have no access to C:\Users\27833\Downloads\Donaldson PDf\ so we can't add to the queries and refresh them, so I've taken your existing results table and processed that. See the query Donaldson_PDfNew which uses the fnMergeRows function. In a part of the function I've used the ¬...

So if you've managed to get the data into the data model, it's only the design of the pivot you need. Supply (a) a workbook with the data-acquiring query/whatever in it, (b) a small sample of the of the text file (sensitive data removed if necessary, but still realistic) for us to experiment...

Same IF statements, just different things to do inside the innermost one. It adds a link that will take you to the blank cell.

You might like this small change: If Len(Application.Trim(cll.Offset(, 1).Value)) = 0 Then Destn.Value = cll.Value NewSht.Hyperlinks.Add Anchor:=Destn.Offset(, 1), Address:="", SubAddress:=cll.Offset(, 1).Address(0, 0, External:=True)...

Try: Sub blah() NewSheetName = "Summary" 'adjust this to a sheet name of your liking. Application.DisplayAlerts = False On Error Resume Next Sheets(NewSheetName).Delete On Error GoTo 0 Application.DisplayAlerts = True Set Newsht =...

Let's have a file with, say 5 sheets in, with realistic data in each with about 10 rows on each sheet. It will answer lots of questions that we'll probably guess (wrongly) the answers to. What version of Excel? A formula such as: =LET(a,VSTACK(Sheet1:Sheet5!$A$1:$B$12),FILTER(TAKE(a,,1)...

Awaiting some kind of acknowledgement…

https://support.microsoft.com/en-us/office/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6 about half way down: Create a 3-D reference Click the cell where you want to enter the function. Type = (equal sign), enter the name of the...

=VSTACK(Sheet2:Sheet1000!1:1)

Excel will implicitly convert numeric to strings and vice versa according to context: =8 + "1" ="8" + 1 ="8" + "1" will all return the number 9 ="8" & 1 =8 & "1" =8 & 1 will all return the string "81" In the formula you use, it makes no difference, so why use quotes at all? The fact that...

=SUMIFS(AR19:AR205,$B18:$B204,">"&DATE(2024,1,1)) The the two ranges need to be the same size.

Dates can be a little difficult for Excel to identify robustly but maybe: =COUNTIFS($B18:$B205,">" & DATE(2020,1,1),C18:C205,4) or shorter: =COUNTIFS($B18:$B205,">43831",C18:C205,4) will both include the count if the dates are after 1/Jan/2020 (but will also count if there's a plain number...

Old thread, I realise, but for anyone searching for alternative methods with the evolving functions in Excel: =AVERAGE(TAKE(SORT(TAKE(FILTER(BG15:BG234,BG15:BG234<>"-"),-20)),8)) or to reduce the range references in the formula to just one...

=COUNTIFS($B18:$B205,"<>",C18:C205,4)copied across.

I'm not at a computer right now but perhaps INT(A2+(1/3)) or INT(CEILING.MATH(A2,1/3)) 1/3 of a day being 8 hours, 16:00 + 8 hours = midnight. I will look more closely later. Edit: later: seems OK, with a difference with what happens at exactly 16:00 where the CEILING formula aligns with your...

Perhaps he did:

I suspect that Sort line should be: Range("A1:H" & LastRow).Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlYes

Conditional formatting in the attached: Edit, taking a leaf from @AliGW the CF formula for Q2:U22 could be a little simpler: =AND($I2<>"",MONTH($I2)=MONTH(Q2),YEAR($I2)=YEAR(Q2))