Search results

=LET(a,IFERROR(CHOOSE({1,2,3},5*4,10*1,22*0),0),AVERAGE(FILTER(a,a<>0)))or=LET(a,IFERROR(CHOOSE({1,2,3},5*4,10*1,22*0),0),AVERAGE(IF(a<>0,a))) Note that 22*0 does not raise an error. Do you really have formulae with values manually typed in like that?!

Me too. Can you confirm which table you want to add a column to? Looking at your data, there's not a single matching 8-character string between the tables, so I haven't the foggiest what I'm looking for.

Yes! I discovered I'd acquired GROUPBY and PIVOTBY only today - I don't know how long I've had it; a week or two perhaps.

fnStuff is my lack of imagination when naming a function! In the attached, I've added a second group of queries called develop function. In it I've shown 2 versions of the query fnStuff (2) and fnStuff (3). fnStuff (2) is the function converted (back) into a plain query fnStuff (3) is the same...

I don't understand the above sentence! The equivalent for ALL in the slicers is the red cross (which means no filtering):

Note the last message was updated just now.

Test: Function FindCodes(ProdCode, Codes) Set dict = CreateObject("Scripting.Dictionary") dict.CompareMode = vbTextCompare RawCodes = Codes.Value 'Filter out blank codes: Count = -1 ReDim Codes(-1 To 0) If IsArray(RawCodes) Then For Each RCode In RawCodes x = Application.Trim(RCode)...

Why re-invent the wheel?

In the attached, run the macro blah. ps. there is no sheet Travel

For Excel2016 I can only suggest a UDF: Function FindCodes(ProdCode, Codes) Dim result As String For Each code In Codes.Cells If InStr(1, ProdCode.Value, code.Value, vbTextCompare) > 0 Then result = result & "+" & code.Value End If Next code If Len(result) > 0 Then result = Right(result...

Try in cell BR2:=TEXTJOIN("+",TRUE,FILTER(Classification!$G$3:$G$5,ISNUMBER(SEARCH(Classification!$G$3:$G$5,Sale!L2)),"STOP"))and in cell BS2:=TEXTJOIN("+",TRUE,FILTER(Classification!$G$3:$G$5,ISNUMBER(SEARCH(Classification!$G$3:$G$5,Sale!X2)),"STOP"))both copied down. Cell BR8 has the only...

I looked at this and didn't reply because my solution was so convoluted that I thought there must be a more elegant formula (I still do!). @mohamed ilyas' solution made me think that my formula wasn't so bad! So in the attached, on Sheet1 there's a single-celled formula at cell D2 (with hints...

Then point no.2 is the way to go.

1. If the spreadsheet is in the same workbook as the Power Query query it can read updated Tables (proper Excel Tables, not just ranges that look like tables) and defined/named ranges. [MCode starts Excel.CurrentWorkbook()] 2. If the spreadsheet is in a different workbook from the workbook which...

I have a 4 year old car. I need to travel 2,000 miles. I'm going to cycle.

Then Power Query is the way to go. I'm not going to re-write what is already a complicated formula into old versions of Excel. These formulae would be totally unmanageable. In the attached a Pivot Table at cell F2 of the Total sheet based on your source data transformed by Power Query...

Try:= Date.From(Number.From([Current Dep Date])-List.Max({0,Number.RoundUp((Number.From([Current Dep Date])-Number.From([Last Dep Date]))/7,0)*7}))or the full MCode to add a column and assign a type to it:= Table.AddColumn(#"Changed Type", "Custom", each Date.From(Number.From([Current Dep...

Of the following worksheet functions, state which are available to you and which are not and I'll try to rewrite: LET LAMBDA UNIQUE FILTER EXPAND HSTACK SUMIFS VSTACK You can check by typing into a cell: = followed by the first few characters of each of the functions, one at a time and a list of...

In the attached, on Sheet Total, formula in cell A3 only, spills down. Hints as to what belongs where: Usual SUMIFS formula in cells B28:B29. You may have to move these down the sheet if you have more source data. You'll note that I've used structured table references (eg. Table1[Supplier...

Change ActiveSheet in the code below to whatever your sheet is: NewHeaders = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense") With ActiveSheet.UsedRange .Rows(1).Interior.Color = RGB(252, 228, 214) With .Cells(1).Offset(, .Columns.Count).Resize(...

Excel 2016 has Get & Transform Data (Power Query) built-in so the attached should work for you. Update the data in the left table then in the right table: right-click and choose Refresh. Separately, @vletm 's function will return a zero if the cell it's working on is empty, but this is easily...

Not at all clear on where you want this, so a guess (this puts it at the bottom of the usedrange):With Ws.UsedRange .Cells(1).Offset(.Rows.Count).Resize(, 6).Value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense") End With or at the top to the right...

cross post: https://forums.excelguru.ca/threads/leave-tracker.11928/

Try: Sub Lookupbyname() Const m = "MESSAGE", S = "Data" Application.ScreenUpdating = False On Error GoTo exitnicely If Not Evaluate("ISREF('" & S & "'!A1)") Then Sheets.Add(, ActiveSheet).Name = S Application.StatusBar = "Add your search list in Sheet 'DATA' column A and proceed!!": Exit Sub...

Same here!