•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
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.
Our take
Managing data across multiple sheets can be frustrating, especially when it feels like you're duplicating efforts. If you're creating a CRM spreadsheet for over 100 clients, optimizing your views is crucial. Your "2026 List View" offers a comprehensive overview, while the "2026 Grid View" provides quick access without the hassle of side-scrolling. However, when formulas fail to copy correctly, it can derail your productivity. Discover efficient techniques to streamline your formulas and eliminate redundant data entry, ensuring both sheets work seamlessly together for enhanced management.
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.
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.
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?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Copy and paste-able dependent dropdown listI have two different setup sheets. First one is a table for my category and my subcategory. Second sheet is basically the same with different input, so not same (sub-)category. On my 3rd sheet i want to have different dependent dropdown lists. E.g. sheet3: 1st row, left cell: dropdown category (sheet1), right cell: dropdown subcategory (sheet1) 2nd row, left cell: dropdown category (sheet2), right cell: Dropdown subcategory (sheet2) Both subcategories should change individually depending on what I selected in their respective left cell. After that I want them to be copy and paste-able in sheet3 while still having fully functional dropdown menus. Some help would be highly appreciated. Nothing I tried so far has worked. edit: my current solution that only works when I don't c&p is to have the first cells on sheet3 as the headers (categories) of the tables as data validation list and the 2nd cell as a formula like this for sheet1: =SORT(UNIQUE(TOCOL(XLOOKUP(Sheet3!Cell1a;Sheet1Categories;Sheet1Subcategories;;0;);1;))) and sheet2: =UNIQUE(TOCOL(XLOOKUP(Sheet3!Cell1b;Sheet2Categories;Sheet2Subcategories;;0;);1;)) I have these on another sheet because I didn't manage to do it on sheet3. Problem is that it still references Cell1a/b if a move it around instead of dynamically changing it. submitted by /u/panox [link] [comments]
- Exporting just one sheet?Looking for a clean way to do this. I've got a spreadsheet with multiple sheets that refence eachother for preparing data for a customer. Obviously I can just print one sheet but what I would really like to do is export the "customer" sheet as a new excel document. Ideally with some of the values becoming their data on the new sheet, but other cells keeping their formulas Example, first box would be what the cells on the export sheet output, next being their formulas. the second set would be how I would like to export, Hard coding the values as regular numbers that are referenced from a different sheet, but keeping the formulas that do math on the same sheet. Is this possible? https://preview.redd.it/cstcpxl78vqg1.png?width=181&format=png&auto=webp&s=f85a19322898ac8a3556d059ba1c5ad6dba7b867 =DifferentSheet!P7 =A1*10 =DifferentSheet!P8 =A2*10 =DifferentSheet!P9 =A3*10 https://preview.redd.it/s4xpp55h8vqg1.png?width=181&format=png&auto=webp&s=962737308118022dbc6a060481514fdc07c1e261 11.5 =A1*10 12 =A2*10 14 =A3*10 submitted by /u/223specialist [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]
- Connecting sheets where I add rows in sheet 1 but want data to stay connected and not change on sheet 2?My title sucks. But here’s my problem. On sheet 1 I have data. Most of is is in row A1, B1, C1…etc. But in sheet 2 I need to break that down into “tiers” so that it shows sheet 1 A1’s data on sheet 2 A1; sheet 1 B1’s data on sheet 2 B2, sheet 1 C1’s data on sheet 2 C3, etc. etc. Then let’s say a new group of numbers in sheet 1 starts at row A3 New group at A5, and so on. Now I know how to connect the cells =sheet1! A1, on sheet 2 etc. The problem is sometimes I need to add rows to sheet 1, but I DON’T want the data in sheet two to change. So like the data I have on sheet 1 A1, B1, C1 has been tiered to sheet 2 but a new set of data comes in and I have to change A1, B1, C1 but now A2, B2, C2 needs to live down on both sheets. How do I ensure that the rows moved down in sheet 1, also live down but stay in alignment with sheet 2? submitted by /u/stretchingmofos [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#rows.com#natural language processing for spreadsheets#AI formula generation techniques#no-code spreadsheet solutions#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#Excel compatibility#business intelligence tools#row zero#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications