Search results

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...

What makes an 8-digit reference a parent? Is it that if it's in the Org_Checker table and has another entry immediately to its right? Or is it that it isn't in the Level 12 (column L)? If it's the former, this is what I get: which is considerably different from your expected results, so what...

And if they're not, as is the case for a shear rate of 500 for example…? (multiply/divide by 10 until in range?!? - tsk tsk)

In the attached, the next version (Chandoo57767WhatIWantToManage_03.xlsx) several changes: 1. This file can be anywhere, even in the same folder as the .csv fles 2. A new table for you to adjust before refreshing the query: at cell H3 a table headed Intervals. These are the intervals to include...

Yes, and I explained why I think this happens, Excel with dots for decimals can (and does) interpret "24,889" as a number but doesn't recognise "25,02" because the thousands separator is in the 'wrong' place, there should be at least 3 characters after the comma, so it remains a string. It's...

@Lenche08, where are you geographically? Is your Excel using commas as decimal separators and dots as the thousands separator? From your screenshots in msg#36 it looks like it is. My solution recognises that the values in the csv file uses commas as the decimal separator (because I told it to...

@vletm's is working here. Perhaps you've got more than one copy of WhatIWantToManage.xlsb and you're running one that isn't in the same folder as the csv files you're extracting data from? If there are no csv files in that folder you'll get the missing data files error message. This is just the...

In the attached, it's as before except: I've shifted the named range Path to cell H1 I've added a table (a proper named Excel Table (called ShearRates)) at cell H3 I've moved the results table to cell J3 (because varying column numbers in the results played havoc with your data in columns A:F)...