•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Excel User Input Checkboxes to Calculate Travel Hours Across Various Time Zones
Our take
Hello Excel gurus! I’m reaching out for assistance with a spreadsheet that calculates travel hours across various time zones. After inheriting this sheet, I noticed it’s now generating value errors due to changes made to accommodate a new PDF flight itinerary format. While I've managed to reverse engineer much of the original functionality, I’m struggling with checkboxes in row 3 that refuse to work, despite my efforts to troubleshoot. Any insights or guidance on how to resolve this issue would be greatly appreciated! Thank you!
Hello Excel gurus!
I have wasted quite a bit of time trying to figure this out. I'm hopeful that Reddit can save me. :)
My department inherited a spreadsheet that calculates how many hours of time we spend traveling. The original spreadsheet was married to the format of a PDF flight itinerary that our travel people used. The travel people changed the spreadsheet (somehow - I don't know how/what - it looks the same to me) so now the old spreadsheet returns nearly all value! errors. The goal is to copy/paste the PDF into A2 and the rest of the sheet does the *magic*. Ultimately, said magic dumps into another tab.
Here's the before/after of the original spreadsheet:
After Clicking Checkbox - Middle column
I dumped the old spreadsheet into Google Sheets to unprotect it and aim to reverse engineer the *magic*. Most of the *magic* is back and jives with the new PDF format. (See below, colorful spreadsheet.)
The only thing (I think - TBD this thread, I guess) I can't seem to figure out is why the checkboxes in row 3 don't work (below, what appears in the snips to be my second row with boxes all checked). Checkboxes in rows 2 and 4 work. I've tried dragging the cells from 2 into 3, from 4 into 3, and I've tried going from column G into F (I've tried every iteration of copy/paste that I know and I've tried every iteration of copy/paste that right clicking offers). Design Mode is off for all of the checkboxes. When I right click the checkbox, the Format Control cell link is linked to the cell of the checkbox (as applicable for each column).
New Spreadsheet Example - All checkboxes checked in row with the issues.
New Spreadsheet Example - All boxes unchecked (no changes that I can tell)
The largest sheet I have made in the past was seven columns.
Any help that can be provided, is much appreciated!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Difficulty with checkbox and hiding rows.Admittedly I’m not great with excel. But I’m trying to setup excel in a way for people to simply click a checkbox on Sheet1, that would automatically filter or hide rows on sheet2, sheet3, and sheet4. Specifically I want to set it up so anyone in the field can open mobile excel on their phone or iPad, simply scroll down an input page with all options available, and quickly click checkboxes for items the customer needs. It will automatically fill data in on other sheets for all the formulas and outputs. And then hide rows on a summary page for the boxes that were not selected. Similarly, hide rows on an “estimate” page so the only rows displayed are the ones clicked. So all the field guys need to do is open mobile excel on their iPhones, click a few checkboxes, and the summary and estimate page will only show rows that were clicked on the input page. For whatever reason, I feel like I’m too dumb for this… so any help is greatly appreciated. Thank you submitted by /u/ALonelyTwinkie [link] [comments]
- Checkbox Issues with VLOOKUPHey guys! I'm in need of some quick help potentially, I'm in the process of completing a spreadsheet for the full list of PKMN TCG cards from the sets. I have a full list of the Pokemon and the # numbers and to the right of the format is the sets themselves. What I want to happen is when I enter in a new set, the formatted spreadsheet will lookup the PKMN name in this new column and tick the checkbox, as I've got a COUNTIF on all of PKMN so I can find out exactly how many are in each set and also how many sets include said PKMN Whenever I copy the formula over to the next column for the set it just shows N/A even though I know there's one in the set. =IF(VLOOKUP("*"&D3,EZ3:KE250,1,FALSE)=D3,"☑️","☐") D3 is the PKMN name EZ3:KE250 is the range of the sets with the list of PKMN https://preview.redd.it/pvtwcyr9fyxg1.png?width=2451&format=png&auto=webp&s=71d2471ed1a4a9b49103450e689391c7f479c1b4 submitted by /u/NoRatio4708 [link] [comments]
- Issue with Excel Power QueryIn my excel workbook I have a long string of queries to get the results I want, however I am noticing a small number of duplicates that SHOULDNT be able to exist. In my first query in this string, I am adding a new column (SelectionBucket), based on two other columns - Works. Then taking this SelectionBucket column, and adding another column (IsRequiredBucket) based on [SelectionBucket] returning one of the required values - Works. I then am adding an index at this time (CourseIndex) - Works. Result: Courses have Index, and SelectionBucket and IsRequiredBucket as options. Q2 (Reference to Q1): Adding Column (IsRequiredCandidate) where [IsRequiredBucket] = True - Works. Filters out to ONLY true values next, and sorted on (Name) (Ascending), (SelectionBucket) (Ascending), (EMark) (Decending) - Works. Result: Filtering down to only RequiredBuckets, sorted by Best to Worst. Q3 (Reference Q2): I group the rows based on (Name) and (SelectionBucket), call it [AllRows]. Add Column (TopRequired) with Table.FirstN(Table.Sort([AllRows], {{"EMark",Order.Descending}}),1) to return the BEST value - Works. Expand the [TopRequired] Table, excluding Name and SelectionBucket - Works. Add column (SelectionType) = "Required" This is where I am not sure if it is working or not, because for 99% of my data, this works. But for some of the entries, this isn't working. Add one more column (IsRequiredSelected) to check (SelectionType), if "Required" = TRUE. Result should be: Selection of one result for each of the buckets available per entry, and setting its (IsRequiredSelected) value to TRUE. Q4 (Reference Q1): I merge Q4(which is just Q1), to Q3, matching on (CourseIndex), Expand the merge (SelectionType). Rename (SelectionType) to (RequiredTag). Add column (IsRequiredSelected) checking [RequiredTag] to return TRUE for "Required", FALSE otherwise. Q5 (Reference Q4): Add Column (IsAdditionalCandidate) checking [IsRequiredSelected] = FALSE. Filter (IsAdditionalCandidate) for only TRUE values. Sort by (Name) (Ascending), (EMark) (Descending). Result: Check if (IsRequiredSelected) = TRUE and clearing them out. Q6 (Reference Q5): Group by Name -> [AllRows] with operation of All Rows. Add column (TopAdditional) coded =Table.FirstN(Table.Sort([AllRows], {{"EMark",Order.Descending}}),7). Expand the table [TopAdditional] excluding (Name). Add column (SelectionType) = "Additional" Result: Taking only records that are marked as "Additional" and taking the best 7 results for each (Name). Q7 is an appended query of combining Q3 and Q6, where it should take the Q3 Results, and adds the Q6 results to it, which should result in NO duplicates. Issue: I am receiving some of my entries as a duplicate through a query check, where I see the one record as both an Additional and as a Required. I am not sure WHY or where it is broken, other than where I think it is... I realize I could have done this in less Queries, however I wanted to verify along each step of the way if something went wrong, so that I could fix it as a portion, instead of having to delete and re-write everything. Please note that I CANNOT share the excel file data itself, as it contains confidential information within it. If I haven't explained a step clearly enough, please let me know and I will try to add further information on it. submitted by /u/DLCamilla [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]
Tagged with
#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#no-code spreadsheet solutions#AI-powered spreadsheet#spreadsheet API integration#Excel alternatives for data analysis#row zero#financial modeling with spreadsheets#Excel compatibility#rows.com#real-time data collaboration#real-time collaboration#Excel alternatives#natural language processing for spreadsheets