Search results

Try: =FILTER(AQ3:AS6,AR3:AR6>=LARGE(AR3:AR6,MIN(5,ROWS(AQ3:AS6))))or to reduce the number of times you need to refer to ranges:=LET(a,AQ3:AS6,b,AR3:AR6,FILTER(a,b>=LARGE(b,MIN(5,ROWS(a)))))or to refer to just one range, maybe:=LET(a,AQ3:AS6,b,CHOOSECOLS(a,2),FILTER(a,b>=LARGE(b,MIN(5,ROWS(a)))))

Cell F2 of Sheet1: =COUNTIF(INDEX('MatchMessenger.matches per day'!$B$2:$DE$44,MATCH(Sheet1!F$1,'MatchMessenger.matches per day'!$A$2:$A$44,0),0),Sheet1!$B2)Copy across/down.

Presumably there is/are formulae affecting the value(s) in cells Q35 and/or C13 when C21 changes? See if this goes some way towards your goal: With Q1 .Range("C21").Value = Application.Min(.Range("C19").Value, 4) Do Until Q1.Range("Q35").Value <= .Range("C13").Value Or .Range("C21").Value =...

=FLOOR.MATH(A2,1/48) format as time. Not sure what you want for the end time but maybe: =CEILING.MATH(B2,1/48)

I've been looking at https://www.mrexcel.com/board/threads/list-all-children-for-a-parent.795669/post-3892723 and have adjusted it a bit and have a sample of results below. Can you check a sample of them (especially some of the big ones) since I need to know that they're correct before I go on:

3 formulae in the attached: B2: =YEAR(EDATE(A2,-3))&"-"&TEXT(EDATE(A2,9),"yy") copied down as far as needed D1: =TRANSPOSE(SORT(UNIQUE(B2:B27))) should spill horizontally D2: =SORT(FILTER($A$2:$A$27,$B$2:$B$27=D$1)) copied across as far as needed

Another method; after changing the source data in column A (making sure it remains part of the table), right-click the table at cell C1 and choose Refresh.

See pivot table on sheet Sheet2 and formulae in columns B & C I've manually added and highlighted 3 dates to verify they're put in the right financial year.

x = [LET(a,DROP(PIVOTBY(Table1[[Parent Name]:[Child Name]],,Table1[Amt],SUM,0,-2),1),HSTACK(IF(CHOOSECOLS(a,2)="",CHOOSECOLS(a,1),CHOOSECOLS(a,2)),CHOOSECOLS(a,3)))] Range("T2").Resize(UBound(x), UBound(x, 2)).Value = xTable1 is the name of my source data table; adjust the code to match your...

Regarding exclusions (which I missed), set up a table somewhere with those exclusions and name the table Exclusions: then adjust the formula in cell F8...

A starter: In F8 of the Monthly Summary sheet: =LET(Colleagues,FILTER(Schedule!$A$6:$A$16,FILTER(Schedule!$B$6:$P$16,Schedule!$B$3:$P$3=F6)=F7,"None"),FILTER(Colleagues,Colleagues<>$C$3))Copy where you need to. You'll probably need to add more if_empty arguments to some of the FILTERs, and...

Do you need to use vba? This is standard pivot table stuff:

Another way in the attached. Will work on the columns headed L, M & H, even if they are not next to each other. Only selecting a cell will not add a tick When revisiting a row to uncheck a box, it will not need to be clicked twice. Downsides: Can be tricked by setting a group of cells to true...

Add a column to convert your textual dates to real excel dates: =DATEVALUE(LEFT(TRIM(A1),10)) then in your pivot you can group the dates into years and months. In the attached, yor text values, converted to dates, pivot with grouping.

If someone requests one day leave being a Friday, does that result in start and end dates being just that one day Friday, or does it include the following Sat and Sun, so Start Fri, end Sun? Similarly, if someone requests Mon to Fri, 5 working days, will the result include both the weekend...

So textbox3 only needs looking at when the user form opens?! So the user doesn't change textbox3? How does the data get into textbox3? It's these sorts of question that would need answering, among probably many others, that your workbook would answer straightaway. I could guess, but I'd probably...

That would mean testing for existence with every change in TextBox3 as the user types. I'm too lazy to try and recreate your setup to test so could you attach a workbook with your setup here please?

I don't think there's an official way to do this here at Chandoo. Some people edit the subject/header of their thread by adding the word 'SOLVED' eg. https://chandoo.org/forum/threads/how-to-add-a-new-column-and-allocate-values-to-it-solved.57619/#post-308895

Something like this:? Private Sub AddKeyToTableList_Click() Dim response As Integer Dim oNewRow As ListRow ' ADD NEW KEY TYPE TO TABLE With Sheets("INFO").ListObjects("Table38") If IsError(Application.Match(Me.TextBox3.Value, .ListColumns(1).DataBodyRange.Value, 0)) Then Set oNewRow =...

Without helper columns:=GROUPBY(YEAR(data[Date]) & " Qtr" & ROUNDUP(MONTH(data[Date])/3,0),data[Amount],SUM)with headers:=GROUPBY(IFERROR(YEAR(data[[#All],[Date]]) & " Qtr" & ROUNDUP(MONTH(data[[#All],[Date]])/3,0),"Year/Qtr"),data[[#All],[Amount]],SUM,3)

Is this you: https://stackoverflow.com/a/79039584?

Are the likes of: "r-001-002.pdf" "r-001-002.STEP" "r-001-002.dwg" the complete names of the files you're looking to move or just the ends of those file names?

In all the csv files you've given us so far there has been a column called 'Shear Rate' (a header, about 24 lines down, (in line with 'Interval data:" on the left.) Was there a csv file in the folder that didn't have such an arrangement of data?

Now we're talking. Much better! Much more robust. I knew of Workbooks.OpenText but hadn't explored the arguments available; definitely something worth remembering. Now to get those date/times into Excel date times!;)

OK, read: 'And if they're not, as is the case for the viscosity values when the shear rates are 500.' Those viscosity values are around "19,57” in the CSV file (from what I can remember) which comes out as 195.7 (one hundred and ninety five point seven) instead of 19.57 (nineteen point five...