•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to filter data on different sheets simultaneously?
Our take
Filtering data across multiple sheets simultaneously can significantly enhance your efficiency when working with complex spreadsheets. If you have an XLS file with several tables that utilize a common identifier, you can create a more streamlined overview sheet by applying filters to each table at once. This approach allows you to focus on specific data, such as all entries marked with identifier "A," while ensuring that your overview sheet recalculates based on the filtered data.
I'm looking for a solution to the following problem i'm facing:
I have an XLS file with a bunch of sheets containing data inside. Each sheet contains a table with data. The data in each table is used to calculate values in a seperate overview sheet within the same file.
Now, here is my question. Each of the tables in the sheets contains a column with an identifier and i would like to use this column to filter the data in the tables in all sheets simultaneuosly and have the data in the overview sheet re-calculated based on the applied filter.
Example:
Each table in each data sheet contains rows that have the identifier "A", but there are also rows of data with different identifiers.
The data in the overview sheet uses all rows of data as a calculation basis.
I want to apply a filter for identifier "A" through every table and have the overview sheet show calculated values for the filitered data only.
I tried searching for solutions, but haven't found anything feasible so far. Hope, you understood my problem and maybe someone knows an elegant solution. If needed, i can provide a sample set of data in the structure i've described.
Thanks a lot in advance!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to confront multiple sheets against each other?English is not my first language, if anything doesn't make sense pls ask. I am currently trying to filter a bigger excel for a conference programm, their file was divided as such sheets: Day 1, Day 2, Day 3, Day 4 -> this containes, for each day in the title of the sheet each conference title for a given room at a given time. I created other sheets, which i named as the main topic. for example: Hybrid if the sheets containes mentions of hybrid as a word in the session or title of the conference. I am now stuck, since I only searched my main interest and created new sheets only for the word I choose. I now have a incomplete subdivision, since I didn't copy each and every conference. I would like to create a new sheets named OTHERS in which I import any conference I don't have in the specific sheets I created. So for example if in Day 1, row7 column 15 never appear in each new sheets I created (hybrid, liquid, ...) I want that row imported in the sheets OTHERS. Is there a way to do this, even only by finding the single row# column# and then I can manually create the table for the new sheets OTHERS. Or I just need to filter manually or use an AI (which i don't like to do since I want to use excel if possible) submitted by /u/Candid-Note6320 [link] [comments]
- 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]
- Assistance with advanced filter optionsI have a report that is being generated through some janky software, and unfortunately the way I am getting the excel document can't be changed. This is a very basic example of what the sheet looks like, the actual generated report is thousands of rows: https://imgur.com/a/d0jNtNx For example, I want to filter for Bob's name in column A and also see all of Bob's associated comments in column B. If I apply a filter for Bob in column A, it hides everything in column B that are the relevant comments (obviously). To clarify, I want a filter to make it look like this: https://imgur.com/a/pgVJsLm And not like this: https://imgur.com/a/wCev8uH Perhaps the filter function isn't the right approach, so I am open to alternatives. submitted by /u/drspudbear [link] [comments]
- Help for a vocab sheet - how to filter ?Hey guys ! Seeking help for a German vocabulary sheet I'm doing to keep track of new words I'm learning. I have a 'master' sheet with everything - but I'd like to create sub categories sheet based on the grammatical function of the words (verbs, gender of the nouns etc...). How do I filter that and keep it updated so that every time I write a new word it automatically comes up in the other pages ? I have multiple columns in the 'master' sheet (see picture). Hopefully what I want to do is clear and you guys can help me, I'm really bad at excel and just trying to make my vocab learning a bit easier ! thanks x https://preview.redd.it/phz11z3369tg1.png?width=2820&format=png&auto=webp&s=74ec8f4f69653c154c908080105a677bbd041388 submitted by /u/mariectu [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#data cleaning solutions#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#rows.com#natural language processing for spreadsheets#financial modeling with spreadsheets#google sheets#enterprise-level spreadsheet solutions#Excel compatibility#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#no-code spreadsheet solutions