Forum Widgets
Latest Discussions
Input 1 to 7 SKU numbers to return groups of rows.
My actual document has 300 cases of product listed in rows, I trimmed it down to 20 cases of product for this example and replaced the names for confidentiality. I want to be able to input these three components 9800006353, 9800006357, 9800006356 and return rows 3-6, 12-15, 24-27. Because the Case SKU in column A has more than just those three components. I want to be able to do this with any of the hundred or so components exist and then return the Case SKU with all of the Component rows. First image is what it would like to start with, then I would input the three components 9800006353, 9800006357, 9800006356 and the second image is what I am looking for. Second image below.robodan70Nov 06, 2024Occasional Reader0Views0likes0CommentsExcel 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 06, 2024Occasional Reader3Views0likes0CommentsWork out hours between DATETIMEs with criteria
Hi experts! As per the title, really cannot get my head around this one. Is it possible to have two datetimes, work out the hours between them with the following criteria: Only include office working hours in the calculation Exclude all weekend days (using NETWORKDAYS?) Exclude all bank/public holidays listed in the file I attach an excel example sheet to hopefully show what I mean. Anyone got any ideas what the formula would look like please? Thank you in advance!matt0020190Nov 06, 2024Brass Contributor10Views0likes1CommentHow to add a conditional formula to a Pivot?
Hello all, I have a Pivot Table where I track my inventory purchases and negative adjustments during the month. I need to see the results only when there is a purchase, so if the Purchase field is blank, it should not be shown. Is there a way to do this? What I’ve been doing is copying and pasting and using formulas. However, this is not practical because ideally, I would like to view the information directly from the Pivot Table. Thanks in advance.enavarrobernalNov 06, 2024Copper Contributor299Views0likes4CommentsReturn Matching Data from Most Recent Date based on condition
I am looking to return the result as Open or Close against the most recent date for each customer code. I have tried several formulas and combination of formulas. Below is the data I'm working with. Date Customer Code Article 01-01-2021 101 AA 01-02-2021 101 DD 01-03-2021 101 CC 01-04-2021 101 CC 01-05-2021 101 DD 01-01-2021 201 BB 01-02-2021 201 CC 01-03-2021 201 DD 01-04-2021 201 DD 01-05-2021 201 AA If everything works well, below is the result I required. Date Customer Code Article Result 01-01-2021 101 AA Close 01-02-2021 101 DD Close 01-03-2021 101 CC Close 01-04-2021 101 CC Close 01-05-2021 101 DD Open 01-01-2021 201 BB Close 01-02-2021 201 CC Close 01-03-2021 201 DD Close 01-04-2021 201 DD Close 01-05-2021 201 AA Open Below is the formula I used to find out the article against latest date with respect to the customer code. =FILTER($C$2:$C$11,($A$2:$A$11=MAXIFS($A$2:$A$11,$B$2:$B$11,B2))*($B$2:$B$11=B2)) But once I incorporated if function for returning open/close, I'm getting the below result which is wrong as highlighted. =IF(FILTER($C$2:$C$11,($A$2:$A$11=MAXIFS($A$2:$A$11,$B$2:$B$11,B2))*($B$2:$B$11=B2))=C2,"Open","Close") Date Customer Code Article Result 01-01-2021 101 AA Close 01-02-2021 101 DD Open 01-03-2021 101 CC Close 01-04-2021 101 CC Close 01-05-2021 101 DD Open 01-01-2021 201 BB Close 01-02-2021 201 CC Close 01-03-2021 201 DD Close 01-04-2021 201 DD Close 01-05-2021 201 AA Open Any help would be appreciated, thank you!jomymathewNov 06, 2024Copper Contributor221Views0likes8Comments=FILTER and =MAX(FILTER issue
I'm Filtering information from this Table but I only want the latest data to show. Why does the =MAX(FILTER formula not populate all the needed data? Could some one please assist, I need all the data from the latest date?millerseth528Nov 06, 2024Copper Contributor270Views0likes6CommentsLine Graph Should Ignore Blank Cells
Greetings, I have an Excel line graph that doesn't seem to be working properly and I'm not sure why. It's acting more like a bug with the Excel file itself - or perhaps the Excel program - than a problem with my graphs and formulas. Click here for my test sheet in Google Drive. My data has a number of blank cells. On the graph, I want any blank cells to just not plot anything. The file is saved in SharePoint/OneDrive. When I open the file from my desktop computer using the local Excel App - not the web app - the blanks display as zero... When I open the file on the browser - not the local Excel App - it does the same thing... When I saved the file as a .xlsx file in Google Drive to share it in this forum post, the file ignores the blanks when it ?opens in Google Sheets?. This is what I want! Here are my settings for Hidden and Empty Cells... I can't get it to work in Excel on the Desktop App which is where I'll be using the file 100% of the time. Any ideas what I'm missing?Flopbot2Nov 06, 2024Occasional Reader4Views0likes0CommentsSchool Master Calendar
Hello I am in charge of putting together a master calendar for our school community. This would be a behind-the-scenes calendar utilized for planning events and spaces within the school. I started out by using a calendar template in Excel called "Family Event Calendar." At first I thought this was going to do EXACTLY what I needed it to do---take a master list of events and apply them to a monthly calendar format in the workbook. Unfortunately, I ran into a problem where only one item will be listed for each day of on the calendar--as we are a school, there are usually a good handful of things on each date. With that, I started working on my own. I have successfully set up a workbook that has a master sheet that lists date, event, start time, end time, location, department, and category of the event. I have set up formatting rules to color code by the category column. I have figured out how to distribute from the master sheet to a separate sheet for each month. This alone will certainly be helpful for us in our planning. However, putting the items into format of a typical month calendar is desired. Here is a viewable link to the document I have put together thus far New Master Calendar Attempt 11 4 2024.xlsxJumbergNov 06, 2024Occasional Reader36Views0likes12CommentsHow to make your formula jump rows
I'm the percentile function to calculate 20 and 80 percentiles from a list of data registered to a date. I need to work it out month by month. However when I drag my formula down it only goes down one row where I need it to go down to the next month. Is it possible to jump down 30 or so rows or is there another way of doing it?nq003524Nov 06, 2024Copper Contributor135Views0likes7Comments
Resources
Tags
- Excel41,519 Topics
- Formulas and Functions24,032 Topics
- Macros and VBA6,238 Topics
- Office 3655,787 Topics
- Excel on Mac2,568 Topics
- BI & Data Analysis2,267 Topics
- Excel for web1,832 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,575 Topics