Forum Widgets
Latest Discussions
Excel Performance Issue with Linked Workbooks - Data Update Delay
Hi Microsoft Community, We are experiencing a performance issue with linked workbooks in MS Excel. Here’s our problem statement: We have two workbooks (Workbook1 and Workbook2) that contain one or more worksheets. Workbook1 (Sheet-1) is linked to Workbook2 (Sheet-1) using standard Excel formulas to connect specific cell data. However, when we update cell data in Workbook1 (Sheet-1), which is linked to Workbook2 (Sheet-1), it takes approximately 10 minutes for the data to reflect in Workbook2. The records we are working with are in the thousands, which may contribute to the delay. We are looking for any possible optimizations or solutions to reduce the time it takes for the data to update. Any guidance or suggestions on how to improve this performance issue would be greatly appreciated. What we tried so far: 1. Direct Link of Workbook Method: Directly link the required range in the formula to the dependent workbook. Problem: Significant delay in updating data. Causes: Empty Rows: Including rows without data slows down the process. Recalculation: Every change triggers a recalculation of all formulas, causing delays. Formula Copying: Copying formulas down to the last row for automatic updates is inefficient. Complex Formulas: Using complex formulas for data fetching is time-consuming. Import Data Using Workbook Link Method: Import data from the dependent workbook and use the local sheet range. Problem: Significant delay in updating data. Causes: Empty Rows: Including rows without data slows down the process. Recalculation: Every change triggers a recalculation of all formulas, causing delays. Formula Copying: Copying formulas down to the last row for automatic updates is inefficient. Import Data Using Power Query Method: Import data from the dependent workbook using Power Query. Problem: Delay in updating data due to limitations in the current version of Excel Online, which does not support Power Query; an updated Office 365 version is required. Causes: Frequent Refresh: Setting auto-refresh to every minute causes frequent, time-consuming refreshes. Formula Copying: Copying formulas down to the last row for automatic updates is inefficient. In addition to these methods, a consultant also attempted to link the workbooks using a hyperlink. Although this method was not fully implemented, I believe it may encounter similar delay issues due to the volume of data and linked dependencies. Thank you in advance for your help! Best Regards, VipinvipinpanwarNov 11, 2024Copper Contributor30Views0likes1CommentComplex numbers in Excel
Did you know that Excel can handle complex mathematical operations with complex numbers? Complex numbers, expressed as "a + bi," where 'a' and 'b' are real numbers and 'i' is the imaginary unit, can be powerful tools in various scenarios. Think engineering, physics, signal processing, or even financial modelling. Excel offers native support for complex numbers, and you can perform basic operations like addition, subtraction, multiplication, and division just as easily as with real numbers. If you haven't explored this yet, give it a shot! It's as simple as entering a formula like=IMSUM(A1:B1)or=IMPRODUCT(A1:B1) One nifty feature is the ability to convert complex numbers from rectangular to polar form and vice versa. This can be a game-changer in certain calculations, especially when dealing with phase angles or magnitude-based analyses. For this you can make use of the=IMABS(A1)and=IMARGUMENT()functions. As complex numbers are considered to be text within Excel you will need special functions to perform arithmetic with them, such as IMSUM, IMSUB, IMPRODUCT, and IMDIV. Excel can help you plot complex numbers on the complex plane using the XY Scatter chart! you will need the IMREAL and IMAGINARY functions to extract the real and imaginary coefficients from the complex number to use them to plot the numbers. This feature can be invaluable when dealing with complex data sets or analysing the behaviour of complex functions. If you're interested in learning more about complex numbers, I made a video covering all functions that work with complex numbers in Excel:https://www.youtube.com/watch?v=_A2DIUibkmk Have you worked with complex numbers before in Excel? What specific use case did you have? Were there any obstacles you had to overcome?LouisDeconinckNov 11, 2024Brass Contributor18KViews1like2CommentsBORDERS USERFORM
Hi, there a way to put round, rectangle borders on Userform? I see a guy on youtube and i notice that, it's very nice the corners on his project. He even put a shadow effect, but on the video he did not teach how to do that. LINK: in case someone want so see https://www.youtube.com/watch?v=X-CY7Gilo5wWLLIANNov 11, 2024Copper Contributor19Views0likes1CommentTransfer Quicken Data into Weekly Report
1/2/2024 1893 Res PAYPAL TRANSFER 240206 Tithe 650.15 1/3/2024 1893 Res PAYPAL TRANSFER 240213 Tithe 96.62 1/4/2024 3022 Youth DEPOSIT MADE IN A BRANCH/STORE Donation 25 1/4/2024 2104 Main DEPOSIT MADE IN A BRANCH/STORE Tithe 0 1/8/2024 2104 Main DEPOSIT MADE IN A BRANCH/STORE Tithe 690 After converting CSV into Excel format and filtering the above, the data represents what must be placed into the appropriate account (column), totaled, and then totaled into weekly segments. We need a formula for a year's worth of data to accomplish this task. There are two other columns not included: 9542 BkSt, and Bldg Fund Week Ending 2104 Main 1893 Res 3022 Youth Total 1/1/24 - 1/6/24 1/7/24 - 1/13/24 1/14/24 - 1/20/24 1/21/24 - 1/27/24 1/28/24 - 2/3/24 2/4/24 - 2/10/24 2/11/24 - 2/17/24 2/18/24 - 2/24/24 2/25/24 - 3/2/24 3/3/24 - 3/9/24 3/10/24 - 3/16/24 3/17/24 - 3/23/24 3/24/24 - 3/30/24 3/31/24 - 4/6/24 4/7/24 - 4/13/24 4/14/24 - 4/20/24 4/21/24 - 4/27/24 4/28/24 - 5/4/24 5/5/24 - 5/11/24InThoughtNov 11, 2024Occasional Reader9Views0likes2CommentsVBA code for Excel form that populates a table
Hello I'm using Office 365 for Windows on a federals government computer system. I track the food and beverage inventory that is stocked from 11 floors and units in a hospital. I had to consolidate it onto one table and complicated me entering data easily. I created a form using Excel VBA to in put data onto a spreadsheet labeled Floor stock data. There are no values assigned to the entry cells. The spreadsheet column names are exactly the same and in the same order as the input form I created. The columns range from A to BR. I need the form to move to the next input box after I hit the enter button on my keyboard. When I push the ENTER button on the form I need to transfer the data to an open row below the previous data. In this case its row 604. I then need to reset the form and clear the data. I need to write the next record on the next row after that. The form needs to reset and the values need to clear after I use the form ENTER button. I've looked online but the videos are not very good and I can't figure anything out from the Excel documentation. Could you help me write the code and give me any references I can use as back up and so I can learn. I'm a complete beginner to Windows Excel VBA and a low intermediate with Excel. Thank youTIndermuehleNov 11, 2024Occasional Reader15Views0likes5CommentsExtracting text removing html/tags and user signatures
Hi experts Hope you can help me! I have a support ticket system that has the issue extracted into one column by ticket. The ticket includes HTML, a users email signature and other text I need to strip out. In affect I am looking just for the plain description. See attached example with column B my desired output. Note that there are two methods that create the data. One is the user utilising a standard template where the description is clear e.g. Description: xxxxxxx Another method is we recieve the data without this mentioned. I have included examples of both scenarios in the file. Is there a reliable formula to basically extract into column B just the ticket description? Note - Macros is not an option sadly due to workplace policies Thank you!matt0020190Nov 11, 2024Brass Contributor27Views0likes3CommentsCHANGE IMAGE WHEN I SELECT ANOTHER NAME ON LIST BOX
Hi, there a way to change the image on userform when i select another name on listbox? I have a shape that change on the worksheet when i select a name on listbox, but on Userform only change when i initialize. The images are in another tab. Here the code on the project: Private Sub UserForm_Initialize() Dim cObj As ChartObject, iPath Application.ScreenUpdating = False With ActiveSheet.Shapes("Imagem 1") Set cObj = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height) .Copy: cObj.Select: cObj.Chart.Paste iPath = Environ("Temp") & "\" & Format(Now, "hhmmss") & ".jpg" cObj.Chart.Export iPath End With Image1.Picture = LoadPicture(iPath) cObj.Delete End Sub Excel 365, Windows,System 64, NotebookWLLIANNov 11, 2024Copper Contributor80Views0likes4CommentsCreating Automated Amendment/Edited Date
Hi There All Just wanted to see if it would be possible to create an automated amendment date on multiple sheets. There are multiple people that edit these shared documents and I would like to see when it was last edited by someone. If this is possible could you please help me out with a solution!GriffinmonNov 10, 2024Copper Contributor33Views0likes4CommentsSearch for multiple keywords in Excel?
Hello, I have a column with many rows containing different texts. I would like to be able to search all the rows in that column for any one of a number of different keywords. For example: I wish to find the rows that contain any of the following keywords: alfa beta gamma But it might not be an exact match. One row might have the following text: find-alfa-value In this case, I want the formula to find this row, as the row contains the keyword "alfa". Thanks!SolvedadminragnarNov 10, 2024Copper Contributor45Views0likes5Comments- ahmedelmessery9Nov 10, 2024Copper Contributor3.3KViews0likes4Comments
Resources
Tags
- Excel41,562 Topics
- Formulas and Functions24,054 Topics
- Macros and VBA6,248 Topics
- office 3655,794 Topics
- Excel on Mac2,568 Topics
- BI & Data Analysis2,270 Topics
- Excel for web1,835 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,576 Topics