•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Built a workbook to track audit prep tasks for my internship and now it's gotten out of hand in a way I don't fully understand anymore
Our take
Managing audit prep tasks can quickly escalate from simple tracking to overwhelming complexity, especially when relying on legacy spreadsheets. If your workbook has spiraled out of control, you’re not alone. While your initial setup was effective, adding multiple layers—like conditional flags and summary counts—can lead to frustrating errors, such as false flags in your status column.
I started simple. One sheet, a list of tasks, a status column with a dropdown, a due date column, conditional formatting to turn rows red when something was overdue. That was fine and I used it every day and it was genuinely helpful. Then I started adding things. I added a summary tab that pulls counts by status using COUNTIF which works fine. Then I added a column for "assigned senior" because I wanted to filter by person, and then I wanted the summary tab to break down by senior too, so I wrote a COUNTIFS with two criteria and that also works. The problem is I then tried to add a column that flags tasks where the due date is within 3 days AND the status is not "complete," and I wrote something that I thought made sense but it keeps returning TRUE on rows where the status actually is complete and I cannot figure out why.
The formula I have in the flag column is =AND(D2-TODAY()<=3, C2<>"Complete") where D is due date and C is status. It looks right to me when I read it out loud but two rows that are marked Complete in column C are still flagging as TRUE and I've checked the spelling three times. I'm wondering if there's a hidden space issue in the dropdown values because I set up the validation list by typing directly into the data validation box and not referencing a range, but I'm not sure how to check that efficently without going cell by cell. The broader question is also whether this whole structure is getting unwieldy and I should just use a proper table with structured references instead of normal ranges, because right now any time I add a row I have to manually drag formulas down and it's starting to feel like I'm fighting the file more than using it.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- My conditional formatting and data validation keeps breaking when it wasn't previously.I have a daily report I have to review and I built a workbook to make the process more digestible. Here is the basic structure of my workbook/worflow: Each day I extract a report to a CSV file. I save it in a designated folder and replace the previous days report. Sheet 1 has a power query mapped to where I save to report I refresh the data which updates the table. I apply some filters/delete items/sort rows (I deleted via deleting table rows). I have a macro button that sends all of my filtered and sorted data to my Daily_Maint table on sheet 2. Sheet 2 Daily_Maint table I have additional columns for manual data. One column on the end for comments (which has no issues). Second to last column is a review status column which has data validation to a list on a separate sheet (Completed, outstanding, system maintenance, circle back). I have (or had) conditional formatting for the list options to color code the list options (green,red, yellow, orange respectively). In another column (one which lists different users) I had conditional formatting to change the color for 2 specific users. In a column that lists client number, I have the cells formatted to insert letters before the numbers. Applied to the whole table I have conduit for atting which it looks at the column with client numbers (aka RM #'s) and when the number is diff from the cell above it, I have formatted a border across the whole table. (This visually separates groups of maintenance by customer). Once I have completed my review, I make a copy of the sheet and rename it to the date of the report. I delete the rows in the Daily_Maint table and do it all again the next day. Recently our drives were remapped and I had to remap my power query. When that happened my macro broke... Which didn't make sense because the macro and power query shouldn't talk to each other. As if by magic the macro started working again. What I'm struggling with: Data validation keeps breaking. This was rarely an issue before the remapping. But now it breaks every day and I can't figure out why. Conditional formatting for color coding keeps breaking. Origioally O tried applying it to =$O:$O which worked well until it didn't. I tried applying it to an absurdly large range like =$O$6:$O$20000 but after I delete rows it changes the applied to formula to what ever number was after the range I deleted. (Ex $O$500:$O$19500) Similar situation with the condition l formatting for the usernames. Tried =$M:$M and this worked really well for months... Until it stopped. Conditional formatting for the line to visually separate groups by customer. My original formula was =$F7<>$F6 applied to the full sheet. This would break every day but it was an easy enough fix I was fine living with. But after talking to AI I tried =INDEX($F:$F,ROW())<>INDEX($F:$F,ROW()-1). I thought it was a permanent fix because I tested it several times. But when I actually use it... It duplicates/breaks/still needs fixing every day. I've tried applying the formatting to "This Table", "This worksheet", and "Current Selection" and nothing is working. What am I doing wrong? Would it be more stable if I built everything on the worksheet and not in a table? I think I'm too close to it at this point to have any perspective and I don't have that much excel experience, most of my knowledge I've gained through building this spreadsheet. I appreciate any advice. submitted by /u/jazzlikebridge42 [link] [comments]
- Trouble coordinating auxiliary sheet in an online workbook to sheet w/ primary dataI have an online workbook that is used to record, monitor, and manage a full year's academic schedule for the college I work for. I have recently become the one in charge of this workbook, and I have spent many hours improving it and making it both more automated and more foolproof. This workbook has several sheets that, at times, reference each other. One sheet is basically the primary data set that shows the actual schedule with 20+ columns of details per row. Another sheet is there for the purposes of tracking and managing non-course-related releases and work that would reduce faculty workload. As such, this data is also listed on the primary sheet ("Master Schedule"). The problem I am having is this; on the auxiliary sheet that is used for tracking non-course-related work of the faculty ("Release Tracking"), all the columns in this table are auto generated based on the data on the Master Schedule, except for column I. Column I is where I manually select (from a data-validated list) a status (pending, accepted, denied, etc.) to assign to the release. The reason I want this data on Release Tracking instead of Master Schedule is because that the number of instances when the condition that triggers something to populate on Release Tracking is only about 3-5% of the total data on Master Schedule. Plus, Master Schedule is already super wide, so avoiding adding more columns to that table is highly preferable. If you haven't guessed already, my trouble is that when the Master Schedule is resorted or the row order of that table is otherwise changed, the status in I of Release Tracking do not move with the rows on that table. I did forget to mention that both Master Schedule and Release Tracking are both formatted as proper tables. I have a hidden helper column in the Master Schedule table that, when a particular condition is met, triggers a unique ID that another hidden helper column in the Release Tracking table can use to populate that table. That formula is: =IF(C12="N/A","MS_"&ROW(),"") The formula in the hidden helper column of Release Tracking is: =IFERROR(INDEX(master_schedule_table[Index ID],AGGREGATE(15,6,(ROW(master_schedule_table[ [ CRN] ])-ROW(INDEX(master_schedule_table[ [ CRN] ],1,1)) + 1)/(master_schedule_table[ [ CRN] ]="N/A"),ROWS($J$2:J2))),"") The formula in the A column of Release Tracking is: =IF(J2="","",IFERROR(INDEX(master_schedule_table, MATCH(J2, master_schedule_table[Index ID], 0), 12), "")) And the rest of the columns (B:H) follow this pattern except that they reference the appropriate correlative column on the Master Schedule. I have spent probably 15-20 hours trying to figure out a solution, but everything I try that allows me to keep the functionality and design of the workbook the way I want it, fails because, ultimately, the formulas on Release Tracking involve elements of relative positioning and because I cannot find a way to write the ID-generating formula in the helper column of the Master Schedule to create an ID with at least a static element and triggers the ID to come and go as the condition in column C of the Master Schedule comes and goes. To summarize what I want, I need a solution that 1) avoids the problem of the rows on Release Tracking changing when the Master Schedule's rows are changed or reordered (thereby linking my status selections to rows other than the one I want that status to be linked to), 2) allows me to manage/change/update the status on Release Tracking instead of Master Schedule, and 3) at least filter the rows on Release Tracking. Release Tracking doesn't necessarily have to be a full table, but for practical purposes, I need to at least be able to filter those rows. Is this possible with an online Excel workbook or am I just spinning my wheels and wasting my time trying to make this happen? submitted by /u/AndrewRyanPatrick [link] [comments]
- How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?Hello all, I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways. What is the problem So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good. Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal. For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" "). To get to the point I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k. These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands. These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet. 'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide. Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions: Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'. Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless. Alternatively: should I use LET whenever a repeated lookup is needed in the same formula? Really looking for to your answers! submitted by /u/EvolvedRevolution [link] [comments]
- Am I playing it too safe with multiple helper columns instead of one complex formula?Hi everyone! I'm a teacher from PH. I started building an Excel system to automate the heavy paperwork we face esp during the EOSY (End of School Year). What began as a "lazy" way to handle my own tasks turned into a tool I shared with three coworkers. From there, I dove into books and YouTube, eventually building a system using basic VBA and complex formulas that my entire school now uses. I'm proud to say it turned weeks of work into just one hour... and since it's summer here, I'm revamping the whole thing to make it available for Mac users while still being limited to Excel 2019 and older (as most teachers don't have the latest ones). I'm proud of the progress but at the moment, I am feeling self-doubt. Just earlier, I decided to lean heavily into using helper columns that my main sheets can reference to. I have quite a few, but each one has a clear, single responsibility. This makes the logic easier for me to follow and debug later. Plus there's a lot to consider really and although I know I could compress a lot of this into fewer, more complex formulas (or even one large formula) but I find this approach much easier to maintain. It's a bit like organizing my own room... I prefer to set things up in a way that I understand so I always know where to look if something goes wrong. That said, I’ve started second-guessing myself while looking at how many helper columns I’m using. Am I playing it too safe (I mean this in a negative way) knowing there are better ways to do it? My question is: In a huge system like this, do you prefer breaking logic into multiple helper columns for clarity, or consolidating everything into fewer, more complex formulas? (I admit I might just be overthinking it (maybe a bit of imposter syndrome kicking in, comparing myself to how others will probably do the same work I'm doing), but I’d really appreciate hearing how others approach this.) submitted by /u/junaliiee [link] [comments]
Tagged with
#rows.com#Excel alternatives for data analysis#financial modeling with spreadsheets#generative AI for data analysis#real-time data collaboration#natural language processing for spreadsheets#AI formula generation techniques#big data management in spreadsheets#conversational data analysis#Excel compatibility#row zero#intelligent data visualization#workbook#summary tab#audit prep tasks#due date#tasks#conditional formatting#COUNTIF#formulas