•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to link data from specific columns on one sheet to another, then sort it column A alphabetically and match the other columns based on corresponding data from Sheet 1
Our take
Are you struggling to pull and sort data from one sheet to another in Excel? You’re not alone! Many users find it challenging to link specific columns and maintain an organized view. In this guide, we’ll explore how to seamlessly connect data from four columns in your primary sheet to a second sheet, ensuring it updates automatically with any changes. Plus, we’ll show you how to sort this data alphabetically while keeping blank cells empty.
I am a beginner at attempting to write formulas in Excel, and I am currently using Excel on a web browser mainly but can and do occasionally edit in the desktop version. I believe the version is Microsoft Office LTSC Professional Plus 2024 in the desktop version, or at least that's the product activated.
I have two sheets on the same spreadsheet, and I want to pull in the data from 4 different columns to the second sheet and have it automatically update when there are changes or additions to the first sheet. I have a couple issues making this happen - the main one is that the data in my first sheet is custom sorted by color, but I want the new sheet to take the data from column B on sheet 1 and sort it alphabetically, and take the other columns and match them correspondingly to the correct values they match to from column B on the first sheet. Secondly, for blank cells, I want it to leave them blank instead of inputting zero, or any dates.
I don't know if this is all even possible, or if I should just remove my custom filters from sheet 1 by cell color and just link it that way. I've spent hours trying to find formulas that work. Please see attached images for reference on what I've tried, and for more clarification as to exactly what I'm requesting. Sorry for the copious redacting, I wanted to be safe rather than sorry with identifiable info regarding this. Any help would be greatly appreciated.
[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]
- Creating a graphic with custom located cells populated by a pivot tableI want to create a graphic with drop down menus that work off of each. So, I select the "Bore size" and the "Class". After I want custom placed cells to populate with the appropriate information based on a separate worksheet that has all of the information for each value. (First Picture Below) Red represents my drop down menus. Once I have populated that, I have a separate spreadsheet (pictured below) with the columns detailed for which variable they are to fill, and it will give me my values accordingly. What terms and capabilities with Excel should I be researching to be able to achieve this? I'm assuming "Pivot Tables" for referencing the information from Sheet 2 to Sheet 1, but I don't know how to get the information to place itself appropriately into the blue sections. Do I have to merge cells to fit there, or can I draw custom cell placement? TIA Also, does anyone know what you would call this type of chart in Excel? The best answer I have so far is "Lookup-Driven Dashboard". Sheet 1 https://preview.redd.it/o4ptbz0ayvmg1.png?width=1325&format=png&auto=webp&s=bddd06bc28023fba707942fd7bdade6341447e7d Sheet 2 https://preview.redd.it/3odfm92ryvmg1.png?width=472&format=png&auto=webp&s=a7c3c27880a79f63916ccc8ff5a1064458bcd239 submitted by /u/West-Doubt6824 [link] [comments]
- I am trying to prevent the end user from having to insert data twice to see it on two different sheets, and the formulas I've been trying aren't copy/pasting well.I have been making a CRM spreadsheet for a finance business with over 100 clients. In trying to optimize the formatting, I have developed two different views which have two different uses. Sheet 1 is "2026 List View" where the informaiton is all on one row per client. It is annoying to side scroll to see all of the information, but it gives the best overall view as to who has paid. https://preview.redd.it/1yinsuhafikg1.png?width=3370&format=png&auto=webp&s=7559a5cfc76b671e2bbcc4b4700c446e2531ce46 Sheet 2 is "2026 Grid View" where the informaiton is organized differently to not need side-scrolling, and gives a better quick per-client view. https://preview.redd.it/2oip1qrbfikg1.png?width=2697&format=png&auto=webp&s=7b4f9f29f34db95d14e17df60efca580ff4c546a I am writing formulas for Sheet 2's cells to reference Sheet 1's data. For example, Sheet 2's cell B3 has ='2026 List View'!A2 and it is working well in terms of the formula. However, once I finished two tables refrerencing cells in rows 2 and then 3, copy/pasting made the next table do the correct columns but in the rows 16 and 17 instead of 3 and 4. I tried continuing the pattern longer, but copy/pasting still made it skip several rows though it is recognizing the correct columns. Is there a different formula I could use to make the copy/pasting more successful? Or, am I stuck doing this cell by cell for 100 rows, making it not worth the hassle? submitted by /u/urgrlB [link] [comments]
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#Excel compatibility#rows.com#cloud-based spreadsheet applications#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#google sheets#row zero#collaborative spreadsheet tools#AI-driven spreadsheet solutions#financial modeling with spreadsheets#automation in spreadsheet workflows