•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Can a User click on a cell in one worksheet and be taken to another that is filtered?
Our take
Navigating Excel can feel overwhelming, especially when you're looking to streamline your workflow across multiple worksheets. In this scenario, you’re aiming to create an interactive experience where clicking a cell in the Main worksheet directs users to the Data worksheet, filtered by "Store Name." While using Macros or VBA might seem daunting, they can facilitate this feature. Additionally, we’ll explore how to directly sum data from the Data worksheet in your Main sheet, bypassing the Pivot entirely. Let’s simplify your Excel journey together.
Good day. I am not too advanced with Excel. I haven't used Macros or VBA but when googling my question, it suggested that may be the solution and when I tried, I couldn't figure it out.
I have 3 worksheets:
- Data
- Pivot: to sum the data and provide a VLOOKUP for Main
- Main: a place for me to combine multiple Data worksheets
My goal is that when a User clicks on a cell in the Main worksheet, they are taken to the Data worksheet AND it is filtered based on criteria, in this case "Store Name".
I attached an image with the results I am hoping to achieve.
Also, is there a way to skip the Pivot and have Main be able to SUM directly from Data?
Thank you for any help!
[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]
- Links to a cell within current worksheet keep changing the location, or doesn't work at all.I have a series of titles at the top of my worksheet with links to the most popular categories, hopefully to get to those categories quickly for data entry. I right click on the cell where I want the link, select Link, the enter the cell reference I want to jump to, with the text to display. This works. It presents a link, and if i click on the link, it jumps to exactly where I want it to go. However, over time, the cell reference isn't correct, and jumps to something else. I assume this is happening due to modifications of the worksheet or what it's from. I do not use "$" in the cell reference, assuming that if I enter A720 as the cell reference, the reference will automatically be modified to A730 if 10 rows are added. I have enough of these links that I shouldn't have to modify all of them each time there is an insertion or deletion. I tried using the formula =HYPERLINK(A730, "Category1") or =HYPERLINK('Shawna Smith'!A730, "Category1"), and neither of these work at all. It does not jump to A730 when I click on it. What am I doing wrong, or should I be using another feature or formula? Thank you for your help. https://preview.redd.it/nb4q3dm883tg1.png?width=649&format=png&auto=webp&s=cfc0c9e7f2d7e9d620075eebf6def8e7500f09fa submitted by /u/KatMagic1977 [link] [comments]
- Using Data from Two Sheets to find classesI work in a corporate job and somehow became the person on my team that knows excel best. I’m okay with excel. Not an expert. But I need help. I have a spreadsheet that lists available training classes and how many seats the class has and how many are still available. I also have a sheet of learner requests for classes. I would love to combine the data and be able to query or pivot to show a list of students that could be enrolled into available classes. What’s the best method of going about that? Thanks. If I need to go elsewhere to ask questions like this please direct me. Thanks. submitted by /u/Coffee4words [link] [comments]
- 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]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#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#data cleaning solutions#natural language processing for spreadsheets#rows.com#Excel compatibility#financial modeling with spreadsheets#Excel alternatives#cloud-based spreadsheet applications