Forum Widgets
Latest Discussions
INDIRECT 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.
Missing Survey Poll Dates
This morning I rebooted my computer and now all the dates in my Scheduling Polls are missing. Instead of showing the correct 6 options across multiple days and times it now shows 6 options on 1 day (not 1 of the correct dates) 6 times that all read the same, 11:52 PM, with "unknown" underneath.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!!
Funçã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,
Can I use Excel to get finance information like Google Sheet?
I use Google sheet to fetch Google Finance data directly for stocks. Is there a way to use MS Excel for the same? I mean can I fetch information from bing finance or something? I have to embed that into my website. I already do that with Google sheet here : https://usafintechzoom.com/COUNTIFS 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!!
