excel
44657 TopicsTop n vs. Others in Excel
Hi all, I'm seeking some help because I'm kind of new to the more intermediate stuff in Excel. I have an Excel table with the following columns: Subcategory in column A, Brand in column B, Region in column C, Year in column D and Values Month in column E. I want to create a PivotTable and a Pivot line chart from this PivotTable that ranks the Top 5 Brands vs. Other Competitors by each region. For added context: There are 5 subcategories, 3 regions and 25 brands. Currently, I've tried grouping the remaining 20 brands as "Other Competitors" vs. the Top 5 brands within a selected region and possibly all regions (when no selection is made). I'm seeking a solution similar to this... Please mind the colours. I will sort those out later. But, the problem that I'm faced with is that upon selection of a region, the PivotTable won't update to the Top 5 brands of a selected region because they've already been grouped. How can I make this more dynamic so that I'm able to show The Top 5 brands vs. Others? Please help. EDIT: My operating system is Windows 10 (64-bit) and I use Excel 365 (Desktop version). For reference, I've attached a link to a sample file. https://1drv.ms/x/c/b2d878e32a062614/IQC1wcnwLICcQasOfnGcwKn0ASjpXp9xQ6rjnOP10Jal5cc?e=HaXEWd Thank you all once again.Solved586Views2likes25CommentsMicrosoft Excel is waiting for another application to complete an OLE action
Hi there, Whenever I copy paste special in excel, my excel file freezes and after a waiting for a few seconds to few minutes, depending on the amount of data being copy pasted, I get this error 'Microsoft Excel is waiting for another application to complete an OLE action' I tried a few things like this but none help and the problem persists. 1st troubleshooting 1. Open the Excel sheet and go the File menu. In the File menu, click on Options. 2. An Excel Options dialog box will appear. Go to the Advanced tab and scroll down to the General area. Over there check Ignore other applications that use Dynamic Data Exchange (DDE). 3. Restart Excel. 2nd troubleshooting Tried opening Excel in safe mode 3rd troubleshooting Removed all the Add ins 4th troubleshooting Reinstalled MS Office. Can someone help.2.6MViews2likes42CommentsINDEX MATCH with VLOOKUP
This is my first time posting here, as I hit a roadblock that I'm sure is simple enough. I am using the following formula, but the source data ('FY26 Income Statement Data'!$A$4:$A$2158) has several rows with the same criteria on 'Cash Flow'!$A7. I think I need to include a VLOOKUP formula but I don't know how to do this. Any help will be appreciated. =INDEX('FY26 Income Statement Data'!$A$4:$AB$2158,MATCH('Cash Flow'!$A7,'FY26 Income Statement Data'!$A$4:$A$2158,0),MATCH('Cash Flow'!C$1,'FY26 Income Statement Data'!$A$4:$AB$4,0))32Views0likes1CommentReturn a value based on different parameters
Not sure if my title is describing my issue correctly. I have one column where each cell contains the same drop down list from which I can choose one of six options, and depending on what option is chosen, I want the cell in another column to return certain value: Option Chosen Return Value Not Started 0% Started 25% In Progress 50% Nearly There 75% Complete 100% I found this example formula online, which is only repeated once but works perfectly for two parameters: =IF(ISNUMBER(SEARCH("Not Started",F3)),"0%",IF(ISNUMBER(SEARCH("Started",F3)),"25%")) However, when I start to extend/repeat the formula more than once for the remaining parameters I cannot get it to work. I've extended it as follows... =IF(ISNUMBER(SEARCH("Not Started",F3)),"0%",IF(ISNUMBER(SEARCH("Started",F3)),"25%",IF(ISNUMBER(SEARCH("In Progress",F3)),"50%",IF(ISNUMBER(SEARCH("Nearly There",F3)),"75%",IF(ISNUMBER(SEARCH("Complete",F3)),"100%")) ... and it returns the error 'the formula is missing an opening or closing parenthesis' Please can anyone help with this. Thank you in advance.20Views0likes1CommentHide rows based on Drop-down box selection
Hello, I have a drop-down box with different selections. One of which is "VPN to VPN Cloud-Based". If this is selected then I want to hide rows 36 through 239. Doing something wrong in my code, as it isn't doing anything when "VPN to VPN Cloud-Based" is select. Any ideas what I need to change? Attaching screenshot of the form and the VBA. Thanks.Solved41Views0likes3CommentsFormulas Not Calculating in Workbook
Hello, I have a very large workbook with many formulas across different sheets. I currently have this workbook set to manual calculations only. And it is saved in a Sharepoint Directory with autosave enabled. When the source data for this workbook is updated. None of the formulas are updating when 1. The Calculate Now button is pressed. 2. The workbook is set back to automatic calculation. 3. When the work book is manually saved and closed. I have noticed another issue. When I go to File > Options. The Options dialogue never appears. I am wondering if anyone else have experienced this issue, and if so have been able to successfully troubleshoot the problem?509KViews0likes6CommentsPaste values-only Excel
I copy data from websites and different software and paste it into Excel 2019. The values are pasted correctly—without the currency symbol or extra space before the numbers—even when using "Paste Values Only." It works as expected. However, in Excel 365, the same data is pasted with the currency symbol "$" followed by a space and then the number, even when I select "Paste Values Only." This causes the values to be stored as General or Text format. To fix this, I have to manually search and replace the symbol and the space. How can I make Excel 365 paste data like Excel 2019 does? Data source: Currency symbol and space before numbers in Excel 365:34Views0likes2CommentsEverchanging spreadsheet
I am in the process of making a spreadsheet for parts on hold. I have cells below the list that I need to stay below any list above without inserting new cells. This first tab with forever change as parts will come and go on the sheet. I also a have separate tabs that show different buyers and I want the information auto transferred to the correct tab by buyer number but keep the main tab whole.19Views0likes1Comment