•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Is it possible to delay VSTACK recalculation?
Our take
In collaborative workbooks using Excel for the Web, performance can suffer when VSTACK recalculates after each individual cell entry. This issue arises especially when you have multiple tables compiling data based on unique identifiers. The challenge is to find a way to delay VSTACK’s recalculation until all six cells of a row are filled and the unique identifier is assigned. Exploring alternative solutions, such as counting assigned identifiers to conditionally control VSTACK’s execution, may provide a creative workaround to enhance efficiency without compromising data integrity.
Using Excel for the Web: I have 4 tables, separated on 4 sheets, in a multi-author workbook, that are used for data entry. Each "entry" is comprised of 6 required cells in a row. Upon completion of a row, each entry is automatically assigned a unique identifier.
These tables are all compiled, using VSTACK, into a separate master sheet. The VSTACK utilizes a FILTER function, so it only compiles entries that have the associated unique identifier.
PROBLEM: My workbook experiences a significant calculation delay after each and every individual cell is entered (not the full row/entry).
It seems that VSTACK reevaluates data after each cell is entered, even when the unique identifier is not yet assigned. Is there a way to delay VSTACK from recalculating until a unique identifier is actually assigned (i.e. until 6 cells of a row are filled)?
Note: PQ is not an option.
Edit: thinking a bit outside the box here - Would it be possible to run a count on all assigned unique identifiers across all tables, then condition the primary VSTACK to =STFU() until that count changes?😅
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How would I make a workbook where the first sheet VSTACKS data from all subsequent sheets and then sorts them by column?Basically, I have a workbook with separate sheets for separate clients, and each sheet has part numbers in it. I want one sheet to summarize all the part numbers in the subsequent sheets. Basically, the VSTACK function to add the following sheets. But where my design gets complicated is this: I want to be able to add a new sheet (basically, create a new customer) and have that new sheet dynamically included in the summary sheet. I.e., I don't want to re-write the VSTACK formula each time I add a new sheet because that will just end up with mistakes being made. Is there a way to dynamically capture ALL following sheets in VSTACK? And second, in the summary sheet, how can I sort the data by a criteria other than listing it in the order of the sheet it appears in? So in other words, I want to summary page to sort by part number, regardless of what customer's sheet it appears in. Thank you so much! submitted by /u/35usc271a [link] [comments]
- My Workbook Has Too Much Going On And Keeps Freezingtl;dr I have my workbook doing too much across several sheets and it freezes for too long, anything I can do to fix it? I am a Customer Services manager for a local pest control company, so I am in charge of the team that makes the schedules. In order to check for scheduling errors, I have found a way to generate reports from our CRM and use Excel to find errors. In the past, I simply generated these reports, sorted through the data myself and gotten the results I wanted. It was great proof of concept, but I wanted to automate it for my convenience and so others who don't know the process can use it when I am unavailable. I was able to make a workbook that would take a list of services due in a month and a list of services scheduled for a day, sort, combine, filter, etc. to produce a list of services scheduled that need another service with it, or where a reservice (extra service requested by a client) is scheduled when a maintenance (services automatically due periodically) is due, so the reservices should just be changed to the maintenance. The problem is that whenever I add/remove the data from those reports, Excel freezes for like 5 minutes while it tried to do all that work. It's better than nothing, but I'd like to find a way for it to not do that, at least not for more than a minute or so. Here is are some details of my Workbook: From our CRM, I generate a report of all services due this month and a report of all services scheduled for the next day. I delete irrelevant columns until I am left with these, in this order: For services due: Customer ID (number unique to each account) Last Name (customer name) First Name (customer name) Subscription Status (will always be "Active") Subscription type (text) Service Due (current due date for the service) For services scheduled: Customer ID (number unique to each account) Last Name (customer name) First Name (customer name) Serviced By (technician assigned to appointment) Service type (text) Scheduled for (date appointment is for) I take each report and put them each into their own sheet in my work book ("SubsDue" and "Scheduled"). Both sheets will have the same number of columns and are in this order to match up intentionally. There can be over 15000 total services due in a month, so I have the sheet work with A2:F200000 to ensure it all fits. Row 1 is always just column headers that do not change. There's a helper column on 2-3 sheets as well, uses COUNTIF to check for duplicate Customer ID's, which is important. I then have it sorting through the data for these sheets by using VSTACK and FILTER to add the data to a new sheet, which will then be used again by even more sheets. Ultimately, I will have 2 sheets at the end that will only have the erroneous services I'm looking for. The sheet does this just like I expect it to, it just freezes up for 5 minutes or so while doing it. Here are a couple examples of the formulas my sheets are using: =FILTER(VSTACK(Scheduled!$A$2:$F$200000),NOT(VSTACK(Scheduled!$E$2:$E$200000)="Reservice")) (Takes the data from another sheet and shows only the Reservices included in it) =FILTER(VSTACK(SubsDue!A2:A200000,ReservicesScheduled!A2:F200000),VSTACK(SubsDue!A2:A200000,ReservicesScheduled!A2:A200000)>0) (Combines the data from 2 sheets and excludes the empty rows form each of them) I've been using Excel at a basic level for years and have learned a little more about it recently, but I don't know what to do to help with this. I can only imagine that having a chain of multiple sheets simultaneously using these formulas on up to 400000 rows at a time is just too much for Excel to handle without freezing up. Any recommendations for how I can improve this? I'd appreciate any help and would love to learn from this. Excel version is 16.0.19725.20152 submitted by /u/Royal-Advantage2706 [link] [comments]