User Profile
SergeiBaklan
Diamond Contributor
Joined 10 years ago
User Widgets
Recent Discussions
Re: Pivot Table: Grouping doesn't work (tried almost everything)
Most often that happens if you have texts which looks like dates or numbers. "formated everything consistently (no text, only numbers..." won't help. If you apply, for example, date format to text like "07/01/2026" it still will be text, not actual date. How exactly did you veryfy if there is text or blank space instead of numbers? Checked cell by cell with ISNUMBER or ISTEXT; or transform possible texts to numbers with Text to Columns; or checked values alignment visually; or something else?Re: Gantt Chart Weekday Function
Depends on logic. If duration 1 day always means next workday after the first worday (e.g. start is sat, first workday is Mon, duration =1, end = Tue which is Mon+1); 0 means start and end at first worday (e.g. for above is Mon), we may use =($D9<=H$5) * (WORKDAY($D9-1, $C9)>=WORKDAY(H$5,-1) )Re: Unprotect in Office Scripts
You may use this ExcelScript.WorksheetProtection interface - Office Scripts | Microsoft Learn interface. Didn't use it before, tested right now on function main(workbook: ExcelScript.Workbook) { const sheet = workbook.getActiveWorksheet(); const protection: ExcelScript.WorksheetProtection = sheet.getProtection() protection.pauseProtection() sheet.getRange("C7:I39") .getSort() .apply( [{ key: 0, ascending: true }] , false , false , ExcelScript.SortOrientation.rows ) protection.resumeProtection() } It works with me, but only with Excel for web, not in desktop version. Didn't check with password, but it shall work, please check documentaion.Re: How can I display negative values for time in calculation results
As a comment, spreadsheets (not only Excel, - Google Sheets, Rows, etc) typically don't work with durations, only with time. And, as was mentioned, there is no such thing in this world as negative time. Thus workarounds - show "time" as decimal number, as text, etc. One more. NOW() returns datetime, not time. LikeRe: Formula help
Carl, formulae could be in column A =LET( MatchRow, SMALL( FILTER( SEQUENCE( ROWS(KeyLog)), ( KeyLog[ID] = $D$6 & "") * ( KeyLog[Out, In, Lost, Found, Defective] = "Out") * ( KeyLog[Column1] = "") ), ROW(A1)), KeyType, INDEX( KeyLog[Key Type], MatchRow), KeyNum, INDEX( KeyLog[Hard Key or Swipe Key Num], MatchRow), IFERROR( IF(KeyType = "Hard Key", "V" & KeyNum, KeyNum), "" ) ) In column B =IF(A8="","", LET( KeyNum, $A8, ID, $D$6 & "", LogKeys, KeyLog[Hard Key or Swipe Key Num], LogIDs, KeyLog[ID], LogTypes, KeyLog[Key Type], LogStatus, KeyLog[Out, In, Lost, Found, Defective], LogDates, KeyLog[Date], LogRooms, KeyLog[Room], MatchRows, FILTER( SEQUENCE( ROWS(LogKeys)), (LogKeys=KeyNum)*(LogIDs=ID)), LastRow, IFERROR(INDEX( MatchRows, COUNT(MatchRows)), NA()), Room, IF( ISNA(LastRow), "", INDEX(LogRooms, LastRow)), Status, IF(ISNA(LastRow), "", INDEX(LogStatus, LastRow)), DateVal, IF(ISNA(LastRow), "", INDEX(LogDates, LastRow)), Type, IF(ISNA(LastRow), "", INDEX(LogTypes, LastRow)), IsAB, IF(Room="", FALSE, OR( RIGHT(Room)={"A","B"})), Description, IF(Type="Swipe Key", IF(IsAB, "Combined Main & BDRM Door Swipe Key", "Main Door Swipe Key"), IF(IsAB, "Combined Main & BDRM Door Hard Key", "Main Door Hard Key") ), IF(Description="", "Unknown -", Description & " - " & Status & ", " & TEXT(DateVal,"m/d/yy")) ))Re: #Name? error in a formula adjusted for circular reference
#NAME? error means you reference on something what Excel is not able to recognize, more details is here How to correct a #NAME? error - Microsoft Support It doesn't matter did you enable iterative calculation or not. Quite often that happens due to typo. Here it could be strange "İ" in front of INDEX. perhaps something else.
