Forum Widgets
Latest Discussions
Missing slots calculation
Dear Experts, I have data like below:- Each Frame(Column A) has 4 corresponding slots(Column B) :- So, A6(955) has entry 3,8,13,18 , which is a complete set, and so on as shown in the ok(complete set) example. In column J, K , I want to populate the Frames(J == SFN == Frames) which are in complete and in Column K, want to populate the slots which are missing in those corresponding Frames. Attached is the Worksheet. Thanks in Advance, Br, Anupam21Views0likes1CommentYear View for Dynamic Calendar
Anyone able to assist with a yearly view for a dynamic calendar? I have a month view which pulls dates associated with a deployment from a table (I got this template from someone in the excel community). I figured I could just copy the month calendar 12 times and just change the months in the drop down but it isnt updating the dates and therefore, not pulling the deployment details. Attached is the file. The first table in the year view sheet is working as that was the same table I copied from the month view sheet. All other table do not work.barbdd12Feb 07, 2025Copper Contributor13Views0likes1CommentAutomatic Rolling 12 Month List
Hi all I've looked first but nothing seems to give me what I am looking for. Hope you can help! I'm simply looking for a rolling 12 months list that automatically updates based on the current date. The financial year starts in March. An example below based on today's date being in February 2025: Column A January 26 February 25 March 25 April 25 May 25 June 25 July 25 August 25 September 25 October 25 November 25 December 25 The idea being that once the month has passed, February 25 would become February 26 in the list and so on. Can this be done with formula based on the today() date? Thank you for any helpSolvedmatt0020190Feb 07, 2025Brass Contributor22Views0likes1CommentAdding existing VBA/Macro in Private Function to new Workbook
Hello again! I have inherited a workbook with a Macro that stores qualitative notes in a separate worksheet and compiles the notes over time. I want to take that Macro and apply it to a new workbook and use the functionality for the same purpose. When I move the Macro into my new workbook, it doesn't come up as an executable process - that is, it doesn't show up in my list of Macros to run. (See below image) I am new to Macros and still learning, but is there some way to get this Macro to run in my new workbook? This is the first part of the Macro which checks the setup of the in use workbook and makes adjustments accordingly. Anytime I've created my own Macro, it starts with the 'Sub' header. I have other Macros in this VBA that begin with 'Sub' and they show up in the list of Macros to run. In this Macro, however, it starts with 'Option Explicit' and then 'Public Function'. There are also parts of the full Macro that begin with 'Private Function'. My thought was to make these available to any workbook that I create, but since they aren't showing up on my Macro list, I can't make the adjustment to have them exist in any workbook. VBA: Option Explicit Public Const sRngName = "PT_Notes" Public Function Check_Setup(ws As Worksheet) As Boolean Dim rNotes As Range, i As Long Dim PT As PivotTable, ptField As PivotField Dim tblNotes As ListObject Dim wsSave As Worksheet '---Check if not exactly one PT on Worksheet- exit If ws.PivotTables.Count <> 1 Then GoTo StopNotes Set PT = ws.PivotTables(1) '---Check if not Compact Report layout- exit For Each ptField In PT.RowFields If Not ptField.LayoutCompactRow Then GoTo StopNotes Next ptField '---Check if Named Range "PT_Notes" doesn't exist- define it If Not NameExists(sRngName, ws.Name) Then With PT.TableRange1 Set rNotes = Intersect(PT.DataBodyRange.EntireRow, _ .Resize(, 1).Offset(0, .Columns.Count)) End With Set rNotes = rNotes.Resize(rNotes.Rows.Count _ + PT.ColumnGrand) ws.Names.Add Name:=sRngName, RefersTo:=rNotes Call Format_NoteRange(rNotes) End If '---Check if "|Notes" Worksheet doesn't exist- add it If Not SheetExists(ws.Name & "|Notes") Then Set wsSave = ActiveSheet Sheets.Add ActiveSheet.Name = ws.Name & "|Notes" wsSave.Activate End If '---Check if Notes DataTable doesn't exist- add it With Sheets(ws.Name & "|Notes") On Error Resume Next Set tblNotes = .ListObjects(1) If tblNotes Is Nothing Then .Cells(1) = "KeyPhrase" .Cells(1, 2) = "Note" Set tblNotes = .ListObjects.Add(xlSrcRange, _ .Range("A1:B2"), , xlYes) End If End With '---Check if any PT fields are not Table Headers - add With tblNotes For Each ptField In PT.RowFields If IsError(Application.Match(ptField.Name, .HeaderRowRange, 0)) Then .ListColumns.Add Position:=2 .HeaderRowRange(1, 2) = ptField.Name End If Next ptField End With Check_Setup = True Exit Function StopNotes: If NameExists(sRngName, ws.Name) Then Application.EnableEvents = False Call Clear_Notes_Range(ws) ws.Names(sRngName).Delete Application.EnableEvents = True Check_Setup = False Exit Function End If End Function Thanks!!burnskrlFeb 07, 2025Copper Contributor42Views0likes3CommentsCheckboxes not automatically updating using COUNTIF
Hey, all, I'm using the following function to set the checkbox boolean of a cell based on the amount of other cells checked: In cell A1: =IF(COUNTIF(A2:A3=2,TRUE),TRUE,FALSE) The formula works, but only when I manually click in and out of the A1 cell. Is there a way to have the cell automatically update like it would in Google Sheets? Using Excel for web build 16.0.18602.42306 here. Appreciate the help!35Views0likes3CommentsHelp creating a custom table in a template
Hi! I'm cannot get formatting a custom table to work. I would really appreciate some help. I have started from a new, blank document and this workflow; Home > Format as a table, but options are either greyed out or the design is not saved. In the preview, every second column is greeen, but when saved in the table gallery all settings are gone. What am I doing wrong?desiree23Feb 07, 2025Copper Contributor39Views0likes1CommentNonfunctioning Table Features in a Partially Protected Excel Sheet
Hi, I want to make my shared excel sheet partially protected. That sheet includes a table as well which I don't want to protect. When I lock only the part I want to protect and unluck the remaining range of columns including the table, I am able to protect only the specific range I want to protect. But then the functions of the table in unlocked area such as automatically resizing when a new data is added becomes non functioning since whole table features freeze when protection on the sheet is active. However I need the people I am sharing the excel sheet with should be able to edit the table but only shouldn't touch the specific locked range which comes before than the unlocked range of columns. How can it be solved?EnKaFeb 07, 2025Occasional Reader18Views0likes1Commentpdf to excel
hi, is it possible to import a formula that is within a pdf document into Excel to then be able to use that formula for calculations? This is to prevent the transcription of complex formulae into the excel document. Thanks.justinausFeb 07, 2025Copper Contributor265KViews0likes9CommentsI need help with three difference searchable dropdown lists
I need help with three searchable dropdown lists on a team info worksheet. I want to make the work searchable with no repetition. The list goes on the worksheets. When a team is picked, the team will not show on the list also no blanke in the dropdown to Bye weeks worksheet Range("B6:B11,E6:E11,H6:H11,K6:K11,N6:N11,Q6:Q11,B14:B19,E14:E19,H14:H19,K14:K19,N14:N19,Q14:Q19,B22:B27,E22:E27,H22:H27,K22:K27,N22:N27,Q22:Q27") NFL playoffs worksheet AFC PLAYOFFS TEAMS SECTION Range("B6,B8,B10,B12,B14,B16,G8”) NFC PLAYOFFS TEAMS SECTION Range(“B20,B22,B24,B26,B28,B30,G22”) Thank Yousf49ers19238597Feb 07, 2025Iron Contributor148Views0likes10Comments
Resources
Tags
- excel42,150 Topics
- Formulas and Functions24,435 Topics
- Macros and VBA6,345 Topics
- office 3655,927 Topics
- Excel on Mac2,613 Topics
- BI & Data Analysis2,325 Topics
- Excel for web1,873 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,605 Topics