Change in return of AVERAGE function - Mac Excel
I was puzzled that a spreadsheet I use daily generated an error today that had not existed in earlier versions. Attempts to confirm the validity of the spreadsheet functions by running prior versions that had previously run error free resulted in the same error. Eventually, it seems that the operation of the AVERAGE function has been changed in an Excel program update that was installed yesterday. Previously, if an AVERAGE function addressed a range of empty cells, it would return a zero value. Now it is returning the error #DIV/0! This is strange because the AVERAGE function will now return zero if the addressed range contains zeros rather than just being empty. Not sure if Microsoft intended this change or if the change might also apply to other functions. In muy case, I'm able to change my spreadsheet to provide for this but it would have been nice to have some warning.Variable "Connecting to Datasource" Load times
Hello, I am running into a strange issue that I am not sure how to solve. I am using Power Query to link to external Excel data files/Sheets and then I process the data within Power Query and also have some Macros that run after the data is refreshed. I am seeing a drastic difference in the amount of time it takes separate users to open/refresh this file (the users have the same internet connection, use the same link to the file on a Shared Drive on our servers, same Excel settings, and have the same hardware). Some users can load and run all Macros in < 15 seconds, while the others take > 10 minutes. I have exhausted all possible causes/reasons that I can think of and am looking for some guidance. Any and all suggestions are appreciated! Thanks, Skywalker_
Help with data and visuals
I hope i can explain this right, i have data that shows 3 things: person, start date and end date: now i need excel to take this information and represent it as the following in the month tracker: It would look for the charts and figure out the timeline and add them to a row with the timeframe: example is Booy 15-0ct-25 to 23-oct-25 the char would go to the next one and add his information, I hope someone can help me. I just need it to populate the month tracker, ThanksHow do you respond to...
Hello my Excellers, I wish you all a happy new year full of great things. I have a question and I am not sure if this is a purely an Excel question or something else but maybe I start here and see how it goes: As you all may have noticed the AI craze is blooming like crazy. If you want to sell lettuce slap AI on its label and step back. I use Bing to search for VBA examples. For example I typed "excel vba code to find cells with formulas" and I get AI to find or "compose" a decent example that with minimal editing will work perfectly. But then at the end of the code and its explanation I sometimes find these kind of questions: "If you want to list the addresses of formula cells instead of highlighting them, I can give you a version that outputs them in the Immediate Window or a new sheet. Do you want me to provide that listing version as well?" How do you say YES I want that code that would list the addresses in a sheet? I see buttons like "Undo" rounded Left Arrow, or "Redo" rounded Right arrow, and a "Copy, "Export", and then "Tweak Content", "Rewrite" button, A "Testing Tools" dropdown with options like "Generate test data" and "Integrate test cases", and another dropdown called "More Actions" with options like "Perform code review", "Explain the code", "Add error handling", and "Make code compliable" and the last dropdown "Work Report" with its own options. But I do not see anything that would allow me to say YES I want that code that would list the addresses in a sheet? Thanks for any insight... GiGi
Remove "Ghost" Excel sheet object from VBA
Hello, I am unable to remove these "Ghost" excel objects left behind after deleting the sheet. I have tried multiple methods that I found on the internet to remove these objects, but nothing works. The sheets are not hidden. The sheets do not existing. That is the "hard coded" name created when the file is created from copying the "CAD" or "CAD_Template". The "remove" button is grayed out. I tried basically everything CoPilot could tell me to remove this. This is what it looks like below. When I run the program, the program creates "CAD4" as the next hard coded name.
Move based on value VBA
I have what I hope to be a fairly easy to solve problem in my VBA code. I have a tab where each row is a different project and each project has a handful of tasks. When the project is complete, the entire row moves to a completed projects tab. My problem is that some of the formulas that make up the tasks update when moved and reference the incorrect cell. I am trying to copy/paste values before the row moves from one tab to the other to no avail. The formula that I am really trying to paste the value of is in column D. Could anyone help me out? Thanks in advance! Dim xRg As Range Dim xCell As Range Dim A As Long Dim B As Long Dim C As Long A = Worksheets("Deliverables").UsedRange.Rows.Count B = Worksheets("Complete").UsedRange.Rows.Count If B = 1 Then If Application.WorksheetFunction.CountA(Worksheets("Complete").UsedRange) = 0 Then B = 0 End If Set xRg = Worksheets("Deliverables").Range("S1:S" & A) On Error Resume Next Application.ScreenUpdating = False For C = 1 To xRg.Count If CStr(xRg(C).Value) = "Complete" Then Sheets("Deliverables").Select xRg(C).EntireRow.Copy Destination:=Worksheets("Complete").Range("A" & B + 1) xRg(C).EntireRow.Delete If CStr(xRg(C).Value) = "Complete" Then C = C - 1 End If B = B + 1 End If Next Application.ScreenUpdating = True Sheets("Deliverables").Select ' End Sub
Moving a column of text data into 3 columns of data?
I have a column of text data cells 1,2,3,4,5,6,7,8,9 and longer. I want to create 3 column of data to graph and manipulate Cell in Columns. 1,2,3 3,4,5 5,6,7 8,9,10 and longer. So i need to create 3 columns of data from 1 column of data. I am using Mac Excel 16 and I can not make this happen. I have tried all sorts of solutions. Help? Thank you,
VBA code just looping despite exit subs
It's been many, many years since I last dabbled with VBA in Excel. I am trying to put some text in I3 when I change a Data Validation List depending on its value. There are a number of values, but my code only looks for NONE, an empty cell (delete the contents), and the rest are weapons. The is a single If Then Elseif Else block. Regardless of what I do, I can't get it to exit to exit the sub and keeps looping. It fills I3 with "Selection is Selection is...until Excel crashes. Here's what Ive got: Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Address = "$B$3" And Target.Value = "NONE") Then Range("I3").Value = "Selection is " & Target.Value & ". You have no Melee weapon." Exit Sub ElseIf Target.Value = "" Then Exit Sub Else Range("I3").Value = "Selection is " & Target.Value & ". This is a Melee Weapon." Exit Sub End If Exit Sub End Sub
