Forum Widgets
Latest Discussions
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 Yousf49ers19238597Feb 07, 2025Iron Contributor122Views0likes10CommentsAdding 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 06, 2025Occasional Reader20Views0likes1CommentFunção SEERRO e PROCV não mostram resultado
Estou trabalhando em uma planilha pronta de controle de férias. Ela tem uma aba de nome Timeline, contendo os nomes dos funcionários e os meses, mesmo com tudo preenchido, nessa aba não aparece nada no campo da função (fórmula). A fórmula que tem é a seguinte: =SEERRO(SE(OU(PROCV($B5;'Controle Calendário'!$B:$R;19;0)=1;PROCV($B5;'Controle Calendário'!$B:$R;20;0)=1;PROCV($B5;'Controle Calendário'!$B:$R;21;0)=1;PROCV($B5;'Controle Calendário'!$B:$R;22;0)=1;PROCV($B5;'Controle Calendário'!$B:$R;23;0)=1;PROCV($B5;'Controle Calendário'!$B:$R;24;0)=1);1;"");"") Como saber o que está errado sendo que eu estou aprendendo agora essas funções? Obrigada,Ligia60Feb 06, 2025Occasional Reader11Views0likes1CommentAutopopulate from (if brochure x then corresponding ID x )
Hello I am creating a template for ordering brochures and gimmicks. I have a list of the names of the brochures, linked to different products, and their respective id number in a sheet. That is 3 separate columns. In a different sheet I am making my order by using dropdown function from the above list using the brochure name (as this is easy to identify). I now want to create a formula so when I choose the name of the brochure in one cell (from the dropdown), the corresponding id is chosen in the adjacent cell (from the list I have in a separate sheet). Ideally I would have another first determinant of the country as 4 countries have different brochures and id numbers but I would like to use the same ordering sheet only changing the name of the country....and then having 4 sheets, one for each country that includes the brochure names and IDs. These lists would then be regularly updated as new brochures and gimmicks are made available. Thank youPastore2025Feb 06, 2025Occasional Reader6Views0likes1CommentI need help!?!
Good day all, Layout - - Workbook 1 will be the main workbook with a multitude of worksheets. - Workbook 2-6 will have a multitude of worksheets with dates as sheet names. The Challenge - How do I get data to pull from workbooks 2-5 to the main workbook based on the sheet names if a date is typed into Workbook 1? The goal is to have Workbook 1 reference Workbooks 2-6 instead of going back and forth. I need to figure out how to change a reference link so that the searched criteria changes the sheet name within a workbook. IS THIS POSSIBLE. Thanks in advance.20Views0likes1CommentSumifs with criteria based on time period
Hi. I have set of values for some time period in the past by days. I want to calculate the values we had before the certain day. I use SUMIFS, but the criteria "<D2" doesn't work. How can I solve it? Many thanks in advance, DamirgdamirFeb 06, 2025Occasional Reader15Views1like1CommentCOUNTIFS to return values in a table
Hello! Working with a large dataset and seeking to break up the data into different columns based on amount. I am looking to separate projects <100K, projects >=100K, projects <=1M, and projects >1M. When I use the COUNTA function, I get a #Spill! error. When I use COUNTIF or COUNTIFS (to specify the 100K<=x<=1M), my output for managers that don't have projects that meet the criteria is '1', rather than the specified 'None'. Here are a few of the solutions that I have tried: =LET( TPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")), CurrentPM, A2, FilteredBudgets, FILTER(Table_Projects[Current Budget GR], (Table_Projects[Project Manager]=CurrentPM) * (Table_Projects[Period]=MAX(Table_Projects[Period])) * (Table_Projects[Current Budget GR]>100000) * (Table_Projects[Current Budget GR]<1000000) ), ProjectsCount, IFERROR(COUNTA(FilteredBudgets), 0), IF(ProjectsCount = 0, "None", ProjectsCount) ) This one gives me a formula error: =LET( TrPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")), CurrentPM, A2, FilteredBudgets, FILTER(Table_Projects[Current Budget GR], (Table_Projects[Project Manager]=CurrentPM) * (Table_Projects[Period]=MAX(Table_Projects[Period])) * ), ProjectsCount, COUNTIFS((FilteredBudgets, ">100000") * (FilteredBudgets, "<1000000")), 0), IF(ProjectsCount = 0, "None", ProjectsCount) ) This one returns the correct project number between 100K and 1M, but does not return "None" for projects that don't meet the criteria. =LET( TrPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")), CurrentPM, A3, FilteredBudgets, FILTER(Table_Projects[Current Budget GR], (Table_Projects[Project Manager]=CurrentPM) * (Table_Projects[Period]=MAX(Table_Projects[Period])) * (Table_Projects[Current Budget GR]>100000) * (Table_Projects[Current Budget GR]<1000000) ), ProjectsCount, COUNTA(FilteredBudgets), IF(ProjectsCount > 0, ProjectsCount, "None") ) Any help is greatly appreciated!!burnskrlFeb 06, 2025Occasional Reader46Views0likes6CommentsFuture Value Formula with Multiple Rates of Increase
Hello, I'm a real estate appraiser, and need to create a formula that brings my comparable sales up to the current date of value. For instance, a comp may have sold on 6/1/2022, but my date of value is 2/5/2025. During this time period, real estate values increased by annual rates of: 6% in 2022, 8% in 2023, 5% in 2024, and 2% so far in 2025. I need a formula which calculates the compounded appreciation as a percentage during the time period. Any input is much appreciated!AppraiserBrent26Feb 06, 2025Copper Contributor42Views0likes2CommentsINDIRECT with Quoted ADDRESS
B1 formula is =INDIRECT("A1"). When A1 value is xyz B1 value is xyz too. If A1 is cut and pasted elsewhere leaving a blank, B1 would become blank instead of referencing wherever xyz has moved to. The above works as intended. Now when B1 is copied to other cells, all targets naturally get the same =INDIRECT("A1"). "A1" with quotes can be obtained in B1 by =""""&ADDRESS(ROW(A1),COLUMN(A1),4)&"""", which yields "A2" with quotes when B1 is copied to B2. But incurs #REF! once nested like =INDIRECT(""""&ADDRESS(ROW(A1),COLUMN(A1),4)&""""). How should B1 be formulated so, when copied, B2 would become =INDIRECT("A2")? Thanking you in advance.ourdogsover20kgFeb 06, 2025Copper Contributor40Views0likes5Comments
Resources
Tags
- excel42,146 Topics
- Formulas and Functions24,432 Topics
- Macros and VBA6,345 Topics
- office 3655,926 Topics
- Excel on Mac2,613 Topics
- BI & Data Analysis2,324 Topics
- Excel for web1,872 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,605 Topics