Search results

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?

I can't test this at the moment but maybe try changing all instances of MONTH in pecoflyer's formula in msg#6 to EOMONTH ?

=FILTER(Sheet1!G2:H7,Sheet1!J2:J7="Yes","None")

Power Query solution at cell F1 in attached. Differences: Cell G5 has removed both matching Dede.Hickmans G6 has more results than yours.

This is an awful solution but I think it works. In the attached: Sheet1: You can delete everything below row 6 in this sheet. Formula in C3, copy down one cell to C4 Formula in cell B4 Copy B4:C4 down as far as you need to. These formulae all use a reference to cell A2 of Sheet2, so on to...

It looks like you're using Excel 2007 ! Is that correct? If you were using a more recent version it would be easy with WORKDAY.INTL which allows you to specify exactly what weekend days are. Am I right that your weekend is only Sunday? Excel 2007 only allows WORKDAY which assumes your weekends...

The 3 bits you mentioned in your first message have already been addressed; those things all pertained to what happens within a cell. To address the new things, the code below: 1. skips blank cells 2.1. removes all bullets wherever they are in the cell (including those not at the beginnng of a...

In this case, if you change type before filling down, all is OK!