Search results

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!

Will conditional formatting alone do?:

try instead: Sub Lookupbyheader() Application.ScreenUpdating = False Dim x As Long, y As Long, empid As Long, LastRow As Long, LastCol As Long, SourceRow As Long, SourceColmNo As Long Dim ws As Worksheet, wsed As Worksheet, red As Worksheet Dim strFile As String Dim TableArray As Range, hRange...

Not sure what you mean by that, so a guess in the attached. In C5 and the same formula as a named lambda in cell C8.

I can't find any merged cells in that attachment. I doubt it; what exactly are you hoping to show? Perhaps a file closer to your actual file?

Change a value in the range O5:O35 (or click button) to update all. You have Kermanshah twice and 4 regions not referred to.

What version of Excel do you have?

Formula at cell B11 of the attached.

Having got myself in front of a computer I see now that I forgot (a) that EOMONTH needs 2 arguments and (b) that EOMONTH won't work as MONTH does with arrays. So, half tested: =IF(EOMONTH(B5,0)<>EOMONTH(B4,0),SUMPRODUCT((MONTH($B$4:$B$33)=MONTH(B4))*(YEAR($B$4:$B$33)=YEAR(B4))*($C$4:$C$33)),"-")

Still can't test but you replaced ALL (4) instances of MONTH in the formula?