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.ourdogsover20kgFeb 06, 2025Copper Contributor36Views0likes5CommentsREPT function not working correctly for values 1 and 2
Hi all, Either I found a bug in a way REPT function work, or I really do not understand something. I'll explain step by step how to reproduce the problem. In A1 we put 0, A2 put 0.1, in A3 0.2 and until A11 you sho1ld have value 1. Now change the type of field to percentage to receive 0% in A1, 10% in A2.... 100% in A11. Now in B1 we put below formula: =REPT("|",A1*10) Now we adapt rest of B cells up till B11. We should have empty B1, one pipe "|" in B2 and so on, 10 characters in B11. It works as desired. Now what I want to do is to have the oposite value in column C. I mean here that in C1 I want to have REPT for 100%, C2 for 90% etc, in C11 0%. So I put below formula in C1 and adapt C2-C11 to have the same: =REPT("_",(1-A1)*10) I should have 10 characters in C1, 9 in C2, and so on. In C10 there should be one character - because (1-0.9)*10 = 0.1*10 = 1 Correct? So in C11 there should be no characters. Well.... it does not work like that for me for last 3 cells. There is 1 character in C9 (should be 2 because (1-0.8)*10 = 2), 0 characters in C10 and zero in C11 (this is ok). Why it does not work for values 1 and 2? Well it works for values, because if I modify C10 to this: =REPT("_",1) then it works. But this does not work: =REPT("_",(1-A10)*10) while A10 = 90%, so 0.9 Why? Can somebody explain it to me? I could understand that REPT does not accept result of other formula as a parameter. But it only does not accept when the result is 0.1 or 0.2 as it works for this for example: =REPT("_",(1-A6)*10) while A6=50% so 0.5 W F T ? !SolvedEternalVoidFeb 06, 2025Occasional Reader28Views0likes3CommentsMissing 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.markwebberFeb 06, 2025Copper Contributor55Views0likes2CommentsAdding 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 Reader1View0likes0CommentsHow to export excel listbox values into a csv file?
How to export excel listbox values into a csv file? TIAdaimneFeb 06, 2025Occasional Reader24Views0likes2CommentsFunçã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 Reader2Views0likes0Commentswill the Excel Labs adv formula editer be microsoft certified?
It is not on the list here Microsoft Excel Apps Security and Compliance - All Apps - Microsoft 365 App Certification | Microsoft Learn, and my organization will not allow us to use it unless it is microsoft certified.mfe92AUFeb 06, 2025Copper Contributor183Views0likes2CommentsCan 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/techguyfromusaFeb 06, 2025Occasional Reader17Views0likes1CommentCOUNTIFS 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 Reader37Views0likes5Comments
Resources
Tags
- excel42,145 Topics
- Formulas and Functions24,431 Topics
- Macros and VBA6,343 Topics
- office 3655,926 Topics
- Excel on Mac2,613 Topics
- BI & Data Analysis2,323 Topics
- Excel for web1,872 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,605 Topics