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, Anupam
Year 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.
Adding 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!!
Checkboxes 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!
Help 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?Nonfunctioning 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?I 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 You
