•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Keep manual row data
Our take
Are you struggling to keep manual data in sync with dynamic recommendations in your spreadsheet? When user inputs on the Questions tab alter the output on the Formulas tab, it can leave your manual selections on the Recommended tab outdated. While the Filter function effectively organizes your data, maintaining accuracy in user-selected requirements becomes challenging. In this guide, we’ll explore solutions to ensure your manual data updates dynamically, keeping your recommendations relevant and accurate as user inputs change. Let’s simplify this process together.
I have a spreadsheet with 4 tabs (Questions, required, recommended, formulas). Based upon how a user answers the questions on the questions tab, it will updates items on the formulas tab to Required or Recommended. I am using the Filter Function to spill data from the formulas tab to the respective tabs(Required/Recommended) based upon the results of the formula for each item. The formulas tab will be hidden from users.
My dilemma is on the Recommended tab I have added a manual column that has a data validation list of Yes/No. I want the user to be able to select if the recommended data listed in A1,A2, etc should be required.
The problem is if an answer on question tab changes it can change the output of what is recommended. The Filter function works great, but the answers they selected in the manual column will be incorrect because they do not dynamically move with the filtered data. Just trying to figure out how to get the manual data to dynamically update as well.
Apologies for the long post.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Sync or map data of two automated columns to the filtering systems of other columnsContext: The automated columns are C (Assigned Codes) and D (Positions). These were transferred from one workbook to another, which is this sheet you're seeing. I used the dynamic array filter function because it has to update in real time, as instructed by my manager. Example, my formula for column C: =FILTER([practicing.xlsx]Sheet1!B:B,[practicing.xlsx]Sheet1!B:B<>"",""") Thus, once the source workbook has more data, it can automatically show in this sheet. Reasons for not using alternatives: Power Query - it's not entirely automatic due to the load every x minutes, and the source workbook has to be closed for it to load in the destination workbook. Power Automate - blocked by my company The Problem: Column C and D aren't linked with the filtering systems of Column A (country) and Column B (Leader Assigned). For example, if the country USA is filtered/selected, then its assigned codes and positions should show. The issue is that their country code (starts with "US") and position, IT, are placed in different rows. If the USA is selected, it will only show rows C3-C4 & D3-D4, which is incorrect. https://preview.redd.it/ursej4pq8jxg1.png?width=916&format=png&auto=webp&s=272aa9d905e6d9e5277c70accc67e2614cc03dbd What I'm looking for: My assigned codes and positions already contain formulas (dynamic array filter function), so using another formula for these columns or in one cell can't be done (I suppose). Is there any way to map the C and D columns to the filtering systems for columns A and B? What I tried doing: Advanced filter - it adds a whole new table, but this sadly isn't what I'm looking for with my data. I want to just use the columns that I have now Custom filter - used the text filter -> begins with. It helps with filtering columns C and D for sure, but it doesn't remap the rows, so the data for columns A and B will appear inaccurate. Please let me know if I am also doing something wrong with what I've tried or done. Thank you in advance, and let me know if anything is unclear. This would really mean a lot to me. I am also open to chatting more! :)) submitted by /u/jeankrstein [link] [comments]
- How do I create dynamic dropdowns that works 2 ways?I have an Order Form sheet set up with a fair bit of code in the background. Part of the Form has order lines with headers of Code, Category and Product Description. I have a separate Products list which I wanted to create the dynamic drop down lists using formulas. I soon found out that you cannot type a FILTER formula directly into the Excel Data Validation source box. After a bit of researching, I figured how to to create dynamic dropdowns by referencing a spill range. The way I've set it up is probably a bit messy (but it works). At the moment the user has to select the category of product they want first. Then the drop down list in Product Description will show only items in that category. When they select the item they want, the drop down list for Code will show the specific code that item relates to. It can work but I was hoping to be able to use partial searches in any of the 3 columns to generate suggestions. eg. Start typing partial item name which would then narrow down options in the drop down list for Products as you type each letter. Select the item and the category and Item Code self populates. Also enable the user to partially type item code or category for a similar result. Hope this makes sense. And if so, any ideas? submitted by /u/AdComplete9707 [link] [comments]
- Change contents of cells based on a drop downSo, I'm trying to generate a spreadsheet to make the forms we use in the office to issue keys. When people are given an apartment, we have a sheet with all the key numbers on it, and then they sign for it. At the moment, we've got 22 different PDFs and Word documents, and it doesn't work. So, what I want to do is this. I've built the template in Excel and created a Reference sheet which will have all the numbers in it. What I am trying to do is set it up so that when you use the Apartment number from a dropdown, it populates with all the key numbers. This sounds like it should be easy to do, but my Excel brain is broken. Advice would be much appreciated! submitted by /u/Hot_Syrup_1774 [link] [comments]
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]